Is there a Text to Rows function instead of Text to Columns?

[rodcard1027]

Hi I'm using Excel 2010 on Windows 7 Pro.
I have a text string that is using the ^ symbol as a delimiter and I need to be able to separate the text into new rows rather than new columns. The formula would also need to create the new rows required so as to not overwrite the next line of data below it. Is this possible without using a macro? I'm not against using one, I just wouldn't know where to start.

Below is a pic of some sample data. The top part is how it's listed, and the bottom (in yellow) is how I would like to have it. Any help or useful tips are greatly appreciated!

Thanks!

 

[cainhill]

Hi team

I've had exactly this same problem while working on a project recently.

Conventional wisdom suggests that there are a couple of ways of dealing with the Text to Rows problem:
- Use the Text to Columns function and transpose the resulting cells into a single column
- Import the Excel document into a database (Access perhaps) and work on the data there
- Struggle with native Excel worksheet functions
- Resort to a VBA solution (I'm backing this solution)

I have made an easy to install Excel add-in, tested on Excel 2007 and 2010 to solve this problem for my own needs, but I've written it up and shared the installer on my site if anyone has this problem in the future.

I'm also keen for feedback and tips for making it better to help us all out.

Please check it out at How to split Excel cells vertically by line.