Solved

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

Posted on 2016-10-22
3
53 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 20

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 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