Solved

data sorting - need help wiith layout (text to column doesnt work)

Posted on 2016-10-22
3
40 Views
Last Modified: 2016-11-13
Hello everyone,

I hope everyone's doing well. I need help to sort out the data and I have a bunch of sheets in excel but there not into each row or column....and it is hard to create formula's because there not in each row or column.

Any suggestions or help is greatly appreciated !

thanks again
test-data-example.xlsx
0
Comment
Question by:aa aa
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 400 total points (awarded by participants)
ID: 41856061
you can use Text to Columns, on the DATA ribbon tab. You have fixed-width data.
Text to Column, on Data ribbon1. select values in column A that need to be parsed.
2. Choose Text to Columns
Text to Column, step 1
3. Scroll across the data. Notice there needs to be a column added between 60 and 70
Text to Column, step 2
4. Click where you want a new column added. Continue until you are satisfied and then click Finish.
Text to Column, step 3
Voila! It isn't perfect because I didn't take enough time to position the breaks better.  
Text to Column, Done
Before you do this, backup your file. Then when you save, you can give it a new name. That way, if you see something needs to be done better, you can get the original data from your backup.

Sometimes you need to run Text to Columns multiple times, on different columns after separation. For instance, maybe leave data that will go to columns I and J combined if that isn't quite right.  Then use Delimited and choose SPACE or DASH for delimiter, and check "Treat consecutive delimiters as one"
Text to Columns, Delimited
Be sure there are enough BLANK columns to the right or separated data will over-write what is there.  Add more columns than you think you'll need, then delete the extra ones when done.
2
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 100 total points (awarded by participants)
ID: 41856643
What Crystal says will work just fine, you just have to repeat the process X times as many as your sheets and tables.
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41885206
Solution provided by Expert
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now