CODEWITHSUNDEEP
excelvbaexcel-2010
Rod
Rod

Reputation: 21

Convert Text to Rows instead of Text to Columns

I have a text string that is using the ^ symbol as a delimiter.

I need to separate the text into new rows rather than new columns.

I need to create new rows 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 to write it.

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 it.

Sample Data Pic

Using Excel 2010 on Windows 7 Pro.

Upvotes: 0

Views: 8235

Answers (2)

Rod
Rod

Reputation: 21

Thanks to those that responded. A friend was able to help by providing the following code:

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

Upvotes: 1

IAmDranged
IAmDranged

Reputation: 3020

Could try something like this:

Sub reArrange()

    Dim inFirstRng As Range
    Dim inRng As Range
    Dim inCur As Variant
    Dim outFirstRng As Range
    Dim outCurRng As Range
    Dim ws As Worksheet

    'CHANGE ARGUMENT TO YOUR SHEET NAME
    Set ws = Worksheets("Sheet2")

    With ws
        'CHANGE ARGUMENT TO WHATEVER THE FIRST CELL OR YOUR DATA INPUT IS IN COLUMN A
        Set inFirstRng = .Range("A3")
        Set inRng = .Range(inFirstRng, inFirstRng.End(xlDown))
        'CHANGE ARGUMENT TO WHATEVER THE FIRST CELL OR YOUR DATA OUTPUT IS IN COLUMN A
        Set outFirstRng = .Range("A9")
        Set outCurRng = outFirstRng
    End With

    For Each cell In inRng.Cells

        inCur = WorksheetFunction.Transpose(Split(cell.Value, "^"))
        outCurRng.Resize(UBound(inCur), 1).Value = inCur

        With ws
            .Range("G" & outCurRng.Row & ":L" & outCurRng.Row).Value = _
            .Range("G" & cell.Row & ":L" & cell.Row).Value
        End With

        Set outCurRng = outCurRng.Offset(UBound(inCur), 0)

    Next cell

    ws.Range("F" & outFirstRng.Row & ":F" & outCurRng.Row - 1).Value = 1

End Sub

Upvotes: 0

Related Questions