This post demonstrates how to split text/value in a cell in different rows in excel.
Of course there should be a separator on the basis of which we would split the values to different rows.
In this digital era where lot of text analysis is going on with the help of different text analysis tool, to achieve that sometimes we are required to organize the data first in a proper format.
Suppose we have some data in cell A1 with semicolon acting as a separator and we want to split the text/values in a cell in excel in different rows in column B as shown in the screenshot below and then these individuals lexicons could be looped through for further processing.
We will use a simple excel macro to split the values into different rows in excel.
Step 1
Go to Developer tab in excel and click on Visual Basic to open the VB editor in which we will write a simple macro to split the text/values in a cell to different rows in excel.
Step 2
The VB editor looks like below and now insert a new module as shown below
Step 3
Now paste the below program into the editor
Sub MakeList()
Dim i As Long, N As Long, j As Long
N = Cells(Rows.Count, “A”).End(xlUp).Row
j = 1
For i = 1 To N
ary = Split(Cells(i, 1), “;”)
For Each a In ary
Cells(j, 2) = a
j = j + 1
Next a
Next i
End Sub
This program uses the concept of nested loop in VBA to split the values in a cell into different rows,
It traverses through each separator (semicolon in our case) and place them into different rows.
Step 4
Now run the program using F8 keyboard shortcut or Run option in the VB editor shown at the top.
After execution you will observe that the values are placed on different rows in column B from B1 based on the semicolon, our separator here.
You could use any separator in place of semicolon by changing the separator definition in our code.
ary = Split(Cells(i, 1), “;”)
So if our separator was @, we would have written the code like this
ary = Split(Cells(i, 1), “@”).
Hope this helped.
