{"id":119,"date":"2018-11-07T18:53:00","date_gmt":"2018-11-07T18:53:00","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=119"},"modified":"2019-02-17T21:08:52","modified_gmt":"2019-02-17T21:08:52","slug":"how-to-parse-with-the-vba-split-function","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2018\/11\/07\/how-to-parse-with-the-vba-split-function\/","title":{"rendered":"How To Parse With The VBA Split Function"},"content":{"rendered":"

So you get a list of names and your boss wants you to separate the last name and the first name into different columns.
\nMany time you want to do this so you can sort by either the first name or last name columns.<\/p>\n

You can use the vba “split” function for this purpose. <\/p>\n

Step 1:
\nFind out the last row in your list.<\/p>\n

\"vba_split_function_1\"<\/a><\/p>\n

Step 2:
\nGo into the VBE editor and set up a module with a For loop.<\/p>\n

Press Alt + F11
\nRight Click and insert module.<\/p>\n

Add this code to your module:<\/p>\n

Sub VBASplitFunction()\r\n    Dim intRow As Integer\r\n    \r\n    Dim strWholeName As String\r\n    Dim varWholeName As Variant\r\n    \r\n    Dim strFName As String\r\n    Dim strLName As String\r\n    \r\n    For intRow = 2 To 28\r\n        strWholeName = Range(\"A\" & intRow)\r\n        \r\n        'the split function creates an array based on the delimiter specified for the parsing\r\n        varWholeName = Split(strWholeName, \" \")\r\n        \r\n        'parse the array into 2 variables\r\n        strFName = varWholeName(0)\r\n        strLName = varWholeName(1)\r\n        \r\n        'add them to the worksheet currently active\r\n        Range(\"B\" & intRow) = strFName\r\n        Range(\"C\" & intRow) = strLName\r\n        \r\n    Next\r\n    \r\nEnd Sub\r\n<\/pre>\n
After you run the code (Press F5 on your keyboard), you’ll have parsed your columns!<\/p>\n
\"vba_split_function_2b\"<\/a><\/p>\n
Let me know if you have any questions<\/p>\n
[simple_contact_form]\t<\/p>\n
****************************************************<\/p>\n\n\n
\n
<\/div>\n