Splitting data from one cell to multiple columns - no fixed length

beergum

New Member
Joined
Mar 20, 2014
Messages
11
Hi,

I need help figuring out an easy way to split data entered in 1 cell (separated by spaces - lengths are not fixed) into their own columns. I have about 100k entries to work with.

Sample data layout (All in Column A): Screenshot by Lightshot

I'm looking for a way to split each line into columns based on their entries. The only piece of information with fixed length is the first entry for each set which is 9 characters long. I need all the entries in each set to be their own columns. Each set is separated by the series of --- and |

Any help would be much appreciated.

Thank you!

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

beergum

New Member
Joined
Mar 20, 2014
Messages
11
Thank you. I tried that but the problem is how I can move the other entries from the set to columns in the same row. Set 1 on the screenshot is from rows 1 - 4. After doing Text to Column, I would need everything on rows 2 - 4 to move to their own columns on row 1.
Also, the data's not entered consistently. Some of them have blanks where there shouldn't be, some of them don't where there should be :|
 

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,749
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top