Split String in Excel using VBA Coding

When it comes splitting the string, word from the mixed data, we normally delimit the data by converting data in Text to Column feature available in Excel Data Tab. However it is possible to delimit or split data as per our requirement by writing piece of VBA Code with simple VBA SPLIT Function. 

I have been using this code in fact created an Add-In where dynamically I use in any kind of data. 

In my code I have given an Input Box where user enter delimiter the character and split the data accordingly. Before that let me show by using "Text to Column" from Ribbon

Even you can split using In-built function like LEFT-MID-RIGHT function, please read my previous linked article

In above gif image you can see how data changes using "Text to Column" from DATA Tab in ribbon. It is important that when you handle the data you are suppose to take care of original data and should remain intact.

Now, let us execute VBA Code step by step

Sub SplitString()
'Declare variable explicitly to hold the values, text
    Dim LastRow As Long         'Determine used Last row number
    Dim strArry() As String     'Holds the Result String value
    Dim strText As String       'Holds the string that we want to split
    Dim strChr As String        'Holds the Indentifier Character to split
    Dim x As Integer            'Determine the number as counter used in For-Next
 

We require 3 string variable, 1 as long and 1 integer variable  

'Give Input Box where user will enter the identifier character to split string
    strChr = InputBox("Provide single character to split string")

You can pass any character which is identifier in your data as shown in above example, I have given / as identifier

'Determine Last used row
    LastRow = Sheets(2)Cells(Rows.Count, "B").End(xlUp).Row
   
    For x = 1 To LastRow
   
        On Error Resume Next   ' Handle errors like if blank cell
 

        strText = Cells(x, 2).Value
        strArry = Split(strText, strChr)
        Cells(x, 3).Value = strArry(0)
        Cells(x, 4).Value = strArry(1)
   
    Next x

End Sub
 

It is always necessary to handle errors in VBA and eliminate flaws in code execution. 

On Error resume Next - executes the next step by ignoring any mismatch  value found in referenced column.

Hope you found useful

You can download practice workbook << VBA SPLIT Function >> on google drive