Split single row into multiple rows based on cell value in excel

Question

I have a single row of data in excel. Data is around 5000+ values.

I want to split this single row into multiple rows.Below is the example of same.

My Single row contains data as follows, 1 2 3 4 5 A 1 2 4 5 9 5 9 A 2 1 4 A etc...

I want this single row to be split after every "A" value it reaches. Output below.

1 2 3 4 5

A 1 2 4 5 9 5 9

A 2 1 4

A Etc...

Can some1 help me as how this can be done? Macro is fine with me. Also I have huge data like 5000+ Values.

You should google vba's `Split` function. You could split on the letter `A` and then concatenate an `A` at the beginning of each resulting array element and put that result in its own cell. — Matt Cremeens, Oct 14 '14 at 14:12
@Avinash Do you mean to say your data is in Single cell i.e 1 2 3 4 5 A 1 2 4 5 9 5 9 A 2 1 4 A etc in Cell A1? Will you share what you have you tried to achieve your output. — Jur Pertin, Oct 14 '14 at 14:14
No Jur, data is not in a single cell. Its like in cells A1 A2 A3 etc... each number i have mentioned is in different cell. — Avinash Aswani, Oct 14 '14 at 14:16

score 1 · Accepted Answer · answered Oct 14 '14 at 14:58

This should do your job. Considering your data to be in Sheet1 and output is generated in Worksheet Sheet2.

 Sub pSplitData()

        Dim rngColLoop          As Range
        Dim rngSheet1           As Range
        Dim wksSheet2           As Worksheet
        Dim intColCounter       As Integer
        Dim intRowCounter       As Integer

        'Consider the data to be in First row of Sheet1
        With Worksheets("Sheet1")
            Set rngSheet1 = .Range(.Range("A1"), .Range("A1").End(xlToRight))
        End With

        Set wksSheet2 = Worksheets("Sheet2")
        intRowCounter = 1
        intColCounter = 0

        'Clear previous output
        wksSheet2.Range("A1").CurrentRegion.Clear

        'Loop through and create output in Sheet2
        With rngSheet1
            For Each rngColLoop In .Columns
                If Trim(rngColLoop) <> "" Then
                    If UCase(Trim(rngColLoop)) <> "A" Then
                        intColCounter = intColCounter + 1
                        wksSheet2.Cells(intRowCounter, intColCounter) = rngColLoop
                    ElseIf UCase(Trim(rngColLoop)) = "A" Then
                        intRowCounter = intRowCounter + 1
                        intColCounter = 1
                        wksSheet2.Cells(intRowCounter, intColCounter) = rngColLoop
                    End If
                End If
            Next rngColLoop
        End With

        Set rngColLoop = Nothing
        Set rngSheet1 = Nothing
        Set wksSheet2 = Nothing

End Sub

score 0 · Answer 2 · answered Oct 14 '14 at 14:16

0

Try using Text to Columns and use A as your delimiter. You'll have to add the A back into the resulting cells.

answered Oct 14 '14 at 14:16

ariscris

533
1
4
19

Ya but text to colums will work if data is in single cell. The data which i have is in A1 A2 A3 etc....its single row but different cells. – Avinash Aswani Oct 14 '14 at 14:17
You could combine them first with the tips here: http://stackoverflow.com/questions/8135995/how-to-merge-all-column-into-one-cell-in-excel – ariscris Oct 14 '14 at 14:24

score 0 · Answer 3 · answered Oct 14 '14 at 14:27

0

If your data is in a single cell, say, cell A1, then you can use vba's split to store all of the individual pieces in an array, split with the letter A.

myArray = Split(Cells(1, 1), "A")

In your example, myArray(0) = 1 2 3 4 5, myArray(1) = 1 2 4 9 5, etc.

So if each of those elements needs to be in its own row, you can concatenate the A back onto the front, like so

cells(2, 1) = "A" & myArray(0)
cells(3, 1) = "A" & myArray(1)

and so forth.

answered Oct 14 '14 at 14:27

Matt Cremeens

4,951
7
38
67

Thanks Matt. But my data is not in single cell. Its in different cell of the same row. – Avinash Aswani Oct 14 '14 at 14:31

score 0 · Answer 4 · answered Oct 14 '14 at 15:02

if your data is like this:

      A    ||  B   ||  C     ||  D   ||
-----------------------------------------
1 ||  one  || two  || three  || four ||
2 ||

then put =TRANSPOSE(A1:D1) in A2, and confirm with Ctrl+shift+enter, which will result in formula that looks like: {=TRANSPOSE(A1:D1)}

and a result that looks like:

      A    ||  B   ||  C     ||  D   ||
-----------------------------------------
1 ||  one  || two  || three  || four ||
2 ||  one
3 ||  two
4 ||  three
5 ||  four

or you you are after a one-off operation you can always: select -> copy ; select empty cell -> paste special -> tick transpose

Split single row into multiple rows based on cell value in excel

4 Answers4

Linked