How to split or delimit data using Excel VBA code ?

Using the below macro, you can split the text in one cell. This work as a delimiter, without using the Text to column feature available in excel.

You can use the below code, anywhere in your VBA coding project, where there is a requirement to split the text with specific symbol reference.

Below is the sample code with sample data as example.

Open new excel worksheet.

Copy below data in Cell A2 or Sheet2.

#>ABC>DEF>GHIJ

#>GHI>JKL>MNO

#>PQR>STUV>WXY

#>ZABC>DEFG>HIJK

#>LMN>OPQ>RSTU

#>W>XY>Z>TT

here in the above data we will use ">", symbol to split the data.

open the excel window VBA module. (check out my post on

• How to use Visual Basic Editor in Excel VBA ?)

Insert new module and copy and paste the below VBA code.


Sub split_text()

'--------------------Define Variables which we are using in the macro-----------------------------
Dim indata As String
Dim i As Integer, j As Integer
Dim out As Variant
Dim endrow As Integer
Dim splitSymbol As String

'----------------------Set values to the variables we need to user---------------------------------------

'Set the split symbol, using which we need to split the data
splitSymbol = ">"

'Find the last row of the data in Sheet 2. Using which we will run a loop starting from row number 2 to end row. In older versions of excel update 65536 instead of 1048576
endrow = Sheet2.Range("A1048576").End(xlUp).Row

' There are 2 loops in below code. First is to loop through the rows of the data and second within the cell to split the data

For j = 2 To endrow
   
'assign the data to indata variable, which we need to split. here A acts as column and j as a row number 
        indata = Sheet2.Range("A" & j).Value

 'you can directly put symbol in code or assign the value as listed below out = split(indata, ">")
        out = Split(indata, splitSymbol)
             
 'The split value is assigned to out. While the UBound function below will detect the maximum number from the out string, which we need to split. i.e. this number will define the number of columns in which data will be populated and the below for loop will run for the same number.

       For i = 0 To UBound(out)

'Below code will post the output in the row assigned to j variable and i will act as column
            Cells(j, i + 1) = out(i)
        Next i

Next j

'This will pop up the message box, once the data split is processed. It will say # of rows data processed sucessfuilly
MsgBox j & " rows of data split process completed sucessfully !", vbInformation

End Sub

Please copy the above code to your vba module and test the same. Do let me know if you want any more information on the same.