How do I get Excel to split a sequence of delimited values in a cell over multiple cells?

 |    A   |   B
1| 1 2  4 |
2|        |

Split over /\s+/:

 |    A   |   B
1| 1      |
2| 2      |
3| 4      |

This is in a pivot table, so I want the pivot table's mechanism to take the split in account, and consider it as if the data came this way from the datasource.

  • 1
    A screenshot would be good for better comprehension. – Mehper C. Palavuzlar Jun 3 '10 at 16:01
  • What does this mean: "Split over /\s+/" – Sux2Lose Jun 3 '10 at 16:55
  • @Sux2Lose Split over one or more whitespace characters. – wilhelmtell Jun 3 '10 at 17:10
  • I know about the "Text to Columns" feature, under the Data tab in Excel 2007, but I wish to split the values to rows, not columns. Not only that, but this feature doesn't work in a pivot table: Excel complains about changing the data. – wilhelmtell Jun 3 '10 at 17:15
  • 1
    I don't think there is a native feature of Excel to do this. You either have to play with the data source (Using text-to-columns and then paste special>values>transpose) or use VBA. – Sux2Lose Jun 3 '10 at 19:58

As user Sux2Lose has mentioned, there is not necessarily a native method of transposing this data in Excel. There is, however, a way to utilize a word processor for this task. (MS Word, TextWrangler, Notepad++, etc)

I assume that the data is currently in the same row across multiple columns, and you are trying to take that same data and format it in Excel across multiple rows. If you copy this set of data into your word processor, the table is lost, but the tab spacing remain. At that point, you can do a find and replace to change the tab spacing into new line spacing

Find: ^t in word, \t in other word processors Replace: ^p in word, \r or \n in other word processors

Once you do this, you will be able to paste your data back into Excel in the appropriate format.

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.