Split Text/Values In Different Rows In Excel

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.

Share The Knowledge

Random Posts

  • Remove Quotes In Excel

    Generally it is observed that we get some embedded quotes or quotation marks in our data specially if it is […]

    Share The Knowledge
  • Vlookup First Word In Excel

    In this post we will see how could we vlookup for first word in a string, here string could be […]

    Share The Knowledge
  • Display Number In K Or M Format In Excel

    To show thousands as K and millions as M in a report is a good practise specially we show large […]

    Share The Knowledge
  • Find If A Cell Has Value Below It In Excel

    excel formula to find if a cell has value below it, excel formula to find if below cell is non blank, excel formula to find if a cell has blank value below it

    Share The Knowledge

Leave a Reply