Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.