Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2016-10-22
3
Medium Priority
?
64 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 24

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 1600 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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

571 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