Formatting data.

[koootzz]

I am using a worksheet to analyse data that I am pasting in from a putty window. Due to the way the data is configured, after using text to columns with space as a delimiter, the last few columns won't match up and will start to spread out over 3 columns.
What I would like to do is get the data to line back up again. I had thought of creating a macro to search for the data I'm looking for (which is a number between 10000 and 99999) in each row, then copy and paste back into the correct columns. Some of the columns contain data that I don't need so overwriting these isn't an issue, except that I'm not sure how to do it.
Or is there a simpler/better way to do this.

I've attached a sample file, the data I need to line up starts in column J but soon moves into I, on other data samples it has moved into K as well. The data in F,G and H is unneeded.

Thanks in advance.

Testdatanet.xls

[JBeaucaire]

A proper sample sheet doesn't need to be 809 rows of data. But it does need to have clear BEFORE/AFTER examples, usually on separate sheets. Slim that down a bit, not too much, make sure all variations are demonstrated, them manually mockup the AFTER based on the shorter set of sample data so we see exactly the final goal, removed columns as well.

[koootzz]

Ahh, sorry about that. I've attached a file in the format you have asked for.


Testdatanet2.xls

[JBeaucaire]

The TEXT TO COLUMNS feature has a "Fixed Width" option you should be using to split those rows correctly, not using the "delimited" option.

We'll use a formula to detect those rows to delete first, delete them, then the Fixed Width Text To Columns does a great job.

Please Login or Register  to view this content.

[koootzz]

Excellent! Thanks very much, this works a treat!

[JBeaucaire]

I'll mark this SOLVED for you. Next time, you can do that with the THREAD TOOLS above.