Splitting concatenated Excel columns at spaces?

Question

An application that I use generates data in the form of a text file, with the values separated by spaces. When I load this into Excel, a column of data I'd like to extract is melded to several other columns, making one chunky supercolumn with too much data in it. It is structured like this:

[number] [word] [hyphen] [number]

If possible, I'd like to split this single column into four columns, one for each data entity. Every data entry is separated by a space. Is it possible to essentially replace the spaces with column dividers?

Answer 1

Excel has a Text to Columns feature that should be able to do that for you. Assuming you have Excel 2007 then select the column you want split, go to the Data tab and click Text to Columns. then select Delimited (Next) and then check only the Space option and click Finish.

Answer 2

When you open the data file, you should be able to select Delimited and choose the column separators. Check "space" to add it to the delimiter set.

If none of your data has spaces inside each of your [number] [word] [hyphen] [number] columns, that should do exactly what you want.

Answer 3

If a pure formula solution is needed, below one can be used. When the string is in A1, the formula will be in A2 and can be copied to right as many as needed. The " " portions should be changed if the delimiter is different than a space.

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),((COLUMNS($A1:A1)-1)*LEN($A1))+1,LEN($A1)))