Create Rows From Comma Separated Cell Values In Excel

In this post we will see how to create rows from comma separated cell values in excel.

Suppose we have values in cells that are separated by comma in column A, at the same time we also have single values in the cell as shown below and we want to create a column from comma separated values in column A so tat the final output would like column B.

Excel showing comma separated string from which rows will be created




Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Open developer tab to write VBA to crate rows from comma separated string

Step 2

Insert a module in which we will write a VBA code to create a column from comma separated values.

Insert a module in the VB editor

Step 3

Now double click the newly created module to open that and paste the below code in the module.

Option Explicit

Sub Macro1()

    Dim fromCol As String

    Dim toCol As String

    Dim fromRow As String

    Dim toRow As String

    Dim inVal As String

    Dim outVal As String

    Dim commaPos As Integer

    ‘ Copy from column A to column B.’

    fromCol = “A”

    toCol = “B”

    fromRow = “1”

    toRow = “1”

    ‘ Go until no more entries in column A.’

    inVal = Range(fromCol + fromRow).Value

    While inVal <> “”

        ‘ Go until all sub-entries used up.’

        While inVal <> “”

            Range(fromCol + fromRow).Select

            ‘ Extract each subentry.’

            commaPos = InStr(1, inVal, “,”)

            While commaPos <> 0

                ‘ and write to output column.’

                outVal = Left(inVal, commaPos – 1)

                Range(toCol + toRow).Select

                Range(toCol + toRow).Value = outVal

                toRow = Mid(Str(Val(toRow) + 1), 2)

                ‘ Remove that sub-entry.’

                inVal = Mid(inVal, commaPos + 1)

                While Left(inVal, 1) = ” “

                    inVal = Mid(inVal, 2)

                Wend

                commaPos = InStr(1, inVal, “,”)

            Wend

            ‘ Get last sub-entry (or full entry if no commas).’

            Range(toCol + toRow).Select

            Range(toCol + toRow).Value = inVal

            toRow = Mid(Str(Val(toRow) + 1), 2)

            inVal = “”

        Wend

        ‘ Advance to next source row.’

        fromRow = Mid(Str(Val(fromRow) + 1), 2)

        Range(fromCol + fromRow).Select

        inVal = Range(fromCol + fromRow).Value

    Wend

End Sub




VBA code to split comma separated string and create rows from them in excel

Now run the program by pressing F5 or by pressing play button in the VB editor, this macro will take comma separated data from column A and convert them into rows in column B as shown in the output below

excel result showing rows are created from comma separated values

Hope this helped.




Share The Knowledge

Random Posts