Split Text in Excel VBA
Photo by Joshua Brown on Unsplash
Excel VBA offers a customised way to split out text within a cell. Larger systems don't always talk to Excel as we would like and it is sometimes necessary to split the contents of a cell into many parts.
Split (string, delimiter, limit, compare)
- String - the string you wish to split or separate.
- Delimiter - the character which divides the string into segments
- Limit - The default for limit is -1 which means every delimiter will be shown as a split. "Lets Party Now" when the delimiter is a space " " would result in a limit of 3.
- Compare - This tells Excel whether to perfom binary or text comparisons. Zero (0) is for performing a binary comparison (default if omitted). Number One (1) is for performing a text comparison.
Here is a basic example of splitting a string;
Dim ar() As String
Dim str As String
ar = Split(str)
In the locals window the split result of the above VBA procedure is as follows;
By default Excel splits on space so the split is the same as typing;
ar = Split(str, " ")
The final two arguments are omitted (Limit and compare as these are optional).
Our next example will bring the Limit into the equation.
Dim ar() As String
Dim str As String
ar = Split(str, " ", 2)
In the Locals window the result looks as follows;
Notice at the second word where the 'Limit' has been placed the text string has been split.
The following procedure is a practical example from a post I assisted in on the Chandoo forum. Someone wanted to split the contents of Cell A2. I have hard coded the contents which were in A2 inside the VBA. The split will occur at start of each space.
Sub SplitIt()
Dim ar() As String
Dim var As Variant
Dim str As String
Dim i As Integer
Dim j As Integer
ar = Split(str)
ReDim var(0, UBound(ar))
For i = 0 To UBound(ar)
Range(Cells(1, 1), Cells(1, UBound(var, 2))) = var
An Excel loop will add the data to a second Variant (Var) which will then be output to from Cell A1:C1, as there are 4 elements of the string
9, 1.25, 9.26
All of the spaces are removed through the procedure. If you wanted the data to appear vertically say from D1 to D3 then change the bottom line to;
Range("D1:D" & UBound(var, 2)) = Application.Transpose(var)
The Attached Excel VBA procedure shows some of these methods.
