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.
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA code to create a column from comma separated values.
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
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
Hope this helped.
