Solved

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

Posted on 2016-10-22
3
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 21

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

687 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