Solved

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

Posted on 2016-10-22
3
46 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 18

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 18

Expert Comment

by:xtermie
ID: 41885206
Solution provided by Expert
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

831 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