Text to Columns feature can help you split text strings in Excel. Using VBA, you can make this process even faster. In this guide, we're going to show you how to split text with VBA in Excel.
Data
In this example, we have rows of data containing data separated by “|” characters. Each part represents an individual column.
Splitting text with VBA
If you are unfamiliar with VBA (Visual Basic for Applications), you can check out our beginner's guide here: How to create a macro in Excel
Split Method
VBA has a function named Split which can return an array of sub-strings from a given string. The function parameters are the string to be split and a delimiter character which separates the sub-strings. In our example, the original strings are in cells in B6:B12 and there are “|” characters between the sub-strings.
The following code loops through each cell in the selected range, uses the Split function to create array of substrings, and uses another loop to distribute the sub-strings into adjacent cells in the same row.
Sub SplitText()
Dim StringArray() As String, Cell As Range, i As Integer
For Each Cell In Selection ‘To work on a static range replace Selection via Range object, e.g., Range(“B6:B12”)
StringArray = Split(Cell, "|") ‘Change the delimiter with a character suits your data
For i = 0 To UBound(StringArray)
Cell.Offset(, i + 1) = StringArray(i)
Cell.Offset(, i + 1).EntireColumn.AutoFit 'This is for column width and optional.
Next i
Next
End Sub
You can copy paste the code above to use it in your workbook. However, make sure to update it depending on your data (i.e. change the delimiter or remove the Cell.Offset(, i + 1).EntireColumn.AutoFit if you do not want to change the column widths).
Text to Columns Method to split text with VBA
In VBA, you can call the Text to Columns feature of Excel. The method can be called for a range. Since we want to split cells by a delimiter, the argument DataType should be xlDelimited. The Destination argument determines the top-left cell for the split substrings. Each delimiter type has its own argument. Since the “|” character is not supported natively, our sample code sets Other as True and OtherChar as “|”.
Sub VBATextToColumns_Other()
Dim MyRange As Range
Set MyRange = Selection 'To work on a static range replace Selection via Range object, e.g., Range("B6:B12")
MyRange.TextToColumns Destination:=MyRange(1, 1).Offset(, 1), DataType:=xlDelimited, Other:=True, OtherChar:="|"
End Sub
Text to Columns Method with multiple delimiters
Text to Columns method allows using multiple built-in delimiters at once. These are Tab, Semicolon, Comma and Space. Set any corresponding argument to True to enable the delimiter. The argument names are same with the character.
The following code can split the sample text by semicolon, comma, and space characters.
Sub VBATextToColumns_Multiple()
Dim MyRange As Range
Set MyRange = Selection 'To work on a static range replace Selection via Range object, e.g., Range("B6:B12")
MyRange.TextToColumns _
Destination:=MyRange(1, 1).Offset(, 1), _
TextQualifier:=xlTextQualifierDoubleQuote, _
DataType:=xlDelimited, _
SemiColon:=True, _
Comma:=True, _
Space:=True
End Sub
