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!

 

[DeezNuts]

grr you said to rows not columns sorry

If no one gives a way there is a method here

 

[rodcard1027]

No worries.

I should add that I have tried a work around by first performing TtoC first then transposing that into individual rows which works when I only have a single text string with blank rows beneath it to transpose into. Unfortunately, it doesn't work when I have multiple strings in consecutive rows such as in my example.

 

[Snakehips]

rodcard1027,

Welcome to Mr Excel.

Are you open to a vba solution?

 

[JoeMo]

Here's a macro you can try (untested) on a copy of your worksheet.

Sub rodcard1027()
Dim lR As Long, i As Long, Ct As Long
lR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = lR To 3 Step -1
    Ct = Len(Cells(i, "A")) - Len(Replace(Cells(i, "A"), "^", ""))
    If Ct > 0 Then
        Cells(i, "A").Offset(1, 0).Resize(Ct, 1).EntireRow.Insert
        With Cells(i, "A").Resize(Ct + 1, 1)
            .Value = WorksheetFunction.Transpose(Split(Cells(i, "A"), "^"))
        End With
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[Snakehips]

Heres another....(Hopefully tested)

Sub Breakout()
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For r = LR To 2 Step -1
Set MyCell = Cells(r, 1)
Arry = Split(MyCell.Value, "^")
For c = 0 To UBound(Arry)
If c > 0 Then MyCell.Offset(c, 0).EntireRow.Insert
MyCell.Offset(c, 0) = Arry(c)
Next c
Next r
End Sub

Hope that helps.

 

[rodcard1027]

Snakehips, I am. I am going to test the code you provided. thanks for the help and the welcome!

 

[rodcard1027]

Thanks a ton Joe. Works like a charm!

 

[rodcard1027]

Snakehips, works beautifully, thanks very much!

 

[JoeMo]

Thanks a ton Joe. Works like a charm!
Snakehips, works beautifully, thanks very much!

You are welcome - glad we could help.