I have an Excel sheet that has six columns that are currently all combined into one column. I need to separate them out but the issue is the first column is words that sometimes are one word and sometimes two. Here is an example:

Twin 70 442 186 310 221
Twin Futon 70 389 160 272 195
XL twin 70 463 196 324 231
XL Twin Futon 70 418 174 293 209
Double 100 590 245 413 295

How can I separate these data sets while keeping the words all in the same columns?

  • If the last five columns are always numbers, you could use regular expressions to create a delimited list. Once you have that, you can copy and paste it into Excel, and it will automatically put them in their own columns. – Louis Nov 3 '13 at 1:13
  • The answers I wrote that might help you are these ones; I'm posting the link because as of now, the answer I would have to write would be too complex because of the lack of fixed width (essentially I would need to search for spaces and tell Excel the first values weren't numbers) – Doktoro Reichard Nov 3 '13 at 1:13
  • If you speak regexp, that might be the easiest way to go. If it's a one-off, Notepad++ is really handy for this sort of thing. Search and replace, inserting tabs between columns. If you want to build the logic into the spreadsheet, and you don't mind resorting to VBA, these functions might be useful: stackoverflow.com/questions/19481175/… – maybeWeCouldStealAVan Nov 3 '13 at 9:51
  • “… the first column is … sometimes … one word and sometimes two.” -- or sometimes three: the fourth example line begins with the three words “XL Twin Futon”, followed by five numbers. – Scott Nov 5 '13 at 1:45

The solution that immediately comes to my mind would be to use the text to columns, delimiting on spaces, as you probably already know how to do.

Once you have done that, then sort the rows based on the third column, this will then put all the rows with 3 text columns together, then combine the 3 text columns together with a formula similar to =A2&" "&B2 into an empty column.

Repeat the process on second column.

You can also achieve this with VBA. I would approach that by separating into a new column based on a numeric character after a space, but this approach is more complicated.

You may also be able to approach this with regex, or Excel filters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.