Excel With VBA

How To Parse With The VBA Split Function

So you get a list of names and your boss wants you to separate the last name and the first name into different columns.
Many time you want to do this so you can sort by either the first name or last name columns.

You can use the vba “split” function for this purpose.

Step 1:
Find out the last row in your list.

vba_split_function_1

Step 2:
Go into the VBE editor and set up a module with a For loop.

Press Alt + F11
Right Click and insert module.

Add this code to your module:

Sub VBASplitFunction()
    Dim intRow As Integer
    
    Dim strWholeName As String
    Dim varWholeName As Variant
    
    Dim strFName As String
    Dim strLName As String
    
    For intRow = 2 To 28
        strWholeName = Range("A" & intRow)
        
        'the split function creates an array based on the delimiter specified for the parsing
        varWholeName = Split(strWholeName, " ")
        
        'parse the array into 2 variables
        strFName = varWholeName(0)
        strLName = varWholeName(1)
        
        'add them to the worksheet currently active
        Range("B" & intRow) = strFName
        Range("C" & intRow) = strLName
        
    Next
    
End Sub

After you run the code (Press F5 on your keyboard), you’ll have parsed your columns!

vba_split_function_2b

Let me know if you have any questions

[simple_contact_form]

****************************************************

admin