Excel VBA String Functions: SPLIT, JOIN, CONCATENATE
Excel VBA Functions to Split a String into Substrings, and Join an Array of Substrings to form a String, with Examples.
Also refer Related Link: Excel VBA String Functions: LEFT, RIGHT, MID, LEN, REPLACE, INSTR, INSTRREV
—————————————————————–
Contents:
Concatenate with & (Worksheet / VBA)
—————————————————————–
The Split Function splits a string expression into specified number of substrings, as delimited by a character(s), which are returned as a zero-based one-dimensional array. Syntax: Split(expression, delimiter, limit, compare). It is necessary to specify only the expression argument while all other arguments are optional.
The expression argument is the string expression which is split into substrings, delimited by character(s) contained within. For a zero-length string (“”) expression, the function returns an empty array with no elements. The delimiter is the string character used to delimit or separate the substrings. The delimiter identifies the substring limits, and if omitted, the space character (” “) defaults to be the delimiter. If delimiter is a zero-length string (“”) , the function returns the entire expression as a single-element array. The limit argument specifies the number of substrings to be returned; and -1 indicates that all substrings are returned. The compare argument specifies the type of comparison to use for evaluating strings – a numeric value or a constant can be specified herein, as detailed below.
You can specify the following arguments for the compare argument: vbUseCompareOption (value: -1) performs a comparison using the setting of the Option Compare statement. vbBinaryCompare (value: 0) performs a binary comparison – string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order – wherein ANSI 1252 is used for English and many European languages), wherein uppercase characters are always less than lowercase characters -> A < B < U < Z < a < b < u < z < À < Û < à < û. vbTextCompare (value: 1) performs a textual comparison – string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z). vbDatabaseCompare (value: 2) performs a comparison based on information in your database (can be used within Microsoft Access only). If you do not specify the compare argument, the comparison is done based on the defined Option Compare statement. Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method – you must specify ‘Option Compare Binary’ or ‘Option Compare Text’ at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary.
Example – Using the vba Split function: return each substring, and its length, on splitting a string; number of occurrences of a character (ie. delimiter) within a string;
Sub SplitFunc_1()
‘using the vba Split function: return each substring, and its length, on splitting a string; number of occurrences of a character (ie. delimiter) within a string;
Dim arr As Variant, varExp As Variant, varDelim As Variant
Dim i As Integer, lElementsLen As Long, lExpressionLen As Long
‘specify string expression which will be split into substrings:
varExp = “she Sells sea shellS”
‘specify delimiter for substrings:
varDelim = “s”
arr = Split(varExp, varDelim)
‘includes an array element representing a sub-string of zero-length before the first character which is the delimiter.
‘returns array size (5) ie. number of elements in the array:
MsgBox UBound(arr) + 1
‘returns the number of occurrences of a character (ie. delimiter) within a string expression – returns 4:
MsgBox UBound(arr)
For i = LBound(arr) To UBound(arr)
‘return each element of the array – these are the substrings into which the string expression is split into – returns “”, “he Sell”, ” “, “ea ” & “hellS”:
MsgBox arr(i)
‘return length of each array element – ‘returns 0, 7, 1, 3 & 5:
MsgBox Len(arr(i))
‘total length of all array elements – returns 16:
lElementsLen = lElementsLen + Len(arr(i))
Next i
‘determine length of string expression which is split – returns 20:
lExpressionLen = Len(varExp)
‘Expression length will be equal to ‘number of delimiter occurrences x length of each delimiter’ plus length of all array elements:
’20=4*1+16
If lExpressionLen = UBound(arr) * Len(varDelim) + lElementsLen Then
‘returns Equal:
MsgBox “Equal”
Else
MsgBox “Not Equal”
End If
End Sub
Example – Using the vba Split function to count & return words within a string – refer Image 1.
Sub SplitFunc_2()
‘using the vba Split function to count & return words within a string – refer Image 1.
Dim arr As Variant, varExp As Variant, varDelim As Variant, varWords As Variant
Dim i As Integer
‘specify string which will be split into substrings – each word in the string is separated by a space:
varExp = ” She sells sea shells near the sea shore. “
‘specify space as the delimiter for substrings:
varDelim = ” “
‘Use the worksheet TRIM function to removes all spaces from text except for single spaces between words.
varExp = Application.Trim(varExp)
arr = Split(varExp, varDelim)
‘returns the number of words (8) in the string:
MsgBox UBound(arr) + 1
‘return each word of the string on a separate line:
For i = 0 To UBound(arr)
If i = 0 Then
‘not to give a line break before the first word:
varWords = arr(i)
Else
varWords = varWords & vbLf & arr(i)
End If
Next i
‘returns each word in a separate line – Image 1:
MsgBox varWords
End Sub
Example – Use the Split function to extract the nth element of an array; extract the site name from a web address; extract file name from full file path.
Sub SplitFunc_3()
‘use the Split function to extract the nth element of an array; extract the site name from a web address; extract file name from full file path.
Dim arr As Variant, varExp As Variant, varDelim As Variant
Dim n As Integer
‘———————-
‘EXTRACT Nth ELEMENT OF AN ARRAY
‘specify string:
varExp = “She sells sea shells near the sea shore.”
‘specify space as the delimiter for substrings:
varDelim = ” “
arr = Split(varExp, varDelim)
‘extract the third element of the above string:
n = 3
‘returns ‘sea’:
MsgBox arr(n – 1)
‘———————-
‘EXTRACT Nth ELEMENT OF AN ARRAY
‘extract the third element of the string “22,456,7,9824,0” – returns 7:
MsgBox Split(“22,456,7,9824,0”, “,”)(n – 1)
‘———————-
‘EXTRACT SITE NAME FROM WEB ADDRESS
‘specify web address:
varExp = “http://www.excelsite.com/vba/userforms/check-box-option-button-toggle-button/20140215.htm”
‘specify delimiter:
varDelim = “/”
arr = Split(varExp, varDelim)
‘extract the third element – the site name without http prefix:
n = 3
MsgBox arr(n – 1)
‘———————-
‘EXTRACT FILE NAME FROM FULL PATH
‘specify full file path of a file on your desktop:
varExp = “C:\Users\James\Documents\Excel\VBA\workbook.xls”
‘specify delimiter:
varDelim = “\”
arr = Split(varExp, varDelim)
‘extract the last element – the file name:
n = UBound(arr) + 1
MsgBox arr(n – 1)
‘Alternatively:
MsgBox arr(UBound(arr))
End Sub
Example – Replace all occurrences of a substring in a string expression, with another substring – using the Split & UBound functions:
To download Excel file with live code, click here.
Function Replace_Str3(var As Variant, varFind As Variant, varReplace As Variant, iOption As Integer) As Variant
‘Replaces all occurrences of varFind in var, with varReplace – using the Split & UBound functions.
Dim iFindCount As Integer
Dim arr As Variant
‘Split function returns a zero-based, one-dimensional array containing a specified number of substrings.
arr = Split(var, varFind, , iOption)
‘if varFind not found within var the array will only have one element and UBound(arr) will be zero:
If UBound(arr) < 1 Then
‘return var string & exit procedure:
Replace_Str3 = var
Exit Function
Else
‘start with a zero-length string:
var = “”
‘loop for the number of occurrences of strFind in str, determined by using UBound function:
‘note that the array size after using the Split function will be UBound(arr) +1, and the number of accurrences (varFind) will be 1 less than the array size ie. UBound(arr).
For iFindCount = 1 To UBound(arr)
‘add each element (except the last) of the array with strReplace:
var = var & arr(iFindCount – 1) & varReplace
Next iFindCount
‘add the last array element after all replacements:
var = var & arr(UBound(arr))
End If
‘return final string:
Replace_Str3 = var
End Function
Sub ReplaceStr3()
‘Replaces all occurrences of varFind in var, with varReplace – using the Split & UBound functions.
Dim var As Variant, varFind As Variant, varReplace As Variant, iOption As Integer
‘var is the string within which varFind is searched & replaced by varReplace:
var = “she Sells sea shellS”
‘varFind is the string to search within var & which will be replaced by varReplace:
varFind = “s”
‘varReplace is the string which replace all instances of varFind within var:
varReplace = “?”
‘in a case-insensitive comparison, if you specify to replace occurrences of “a” then instances of both “A” and “a” will be replaced.
‘to perform a binary comparison (case-sensitive), use value 0:
‘iOption = 0
‘to perform a text comparison (case-insensitive), use value 1:
iOption = 1
‘if var is Null, exit procedure:
If IsNull(var) Then
MsgBox “var is Null, exiting procedure”
Exit Sub
‘if var is not Null:
Else
‘if either varFind or varReplace are Null or varFind is a zero-length string:
If IsNull(varFind) Or IsNull(varReplace) Or varFind = “” Then
‘return var without any replacements & exit procedure:
MsgBox var
Exit Sub
Else
‘if none of var, varFind & varReplace are Null, run the function to replace all instances of varFind:
MsgBox Replace_Str3(var, varFind, varReplace, iOption)
End If
End If
End Sub
The Join Function joins the substrings contained in an array, and returns a string with the substrings separated by a delimited character(s). Syntax: Join(sourcearray, delimiter). It is necessary to specify the sourcearray argument while the delimiter arguments is optional. The sourcearray argument is a one-dimensional array that contains the substrings which are to be joined to return a string. The delimiter is the string character used to separate the substrings that are joined to return a string, and if omitted, the space character (” “) defaults to be the delimiter. If delimiter is a zero-length string (“”), the function joins the substrings with no delimiter.
Concatenate with & (Worksheet / VBA)
You can use the “&” operator to concatenate multiple substrings into a single string, both as a Worksheet Function and as a VBA function.
Example – Concatenate with ‘&’ operator in VBA:
Sub ConcatenateStrings()
Dim str1 As String, str2 As String
str1 = “James”
str2 = “Bond”
‘returns “JamesBond”:
MsgBox str1 & str2
‘returns “James Bond”:
MsgBox str1 & ” ” & str2
‘returns “James Bond in Australia”:
MsgBox str1 & ” ” & str2 & ” in Australia”
End Sub
Example – Using the vba JOIN Function, and CONCATENATE with &:
Sub Join_Concatenate_1()
‘using the vba JOIN Function, and CONCATENATE with &.
Dim arr As Variant, varJoin As Variant, varConctnt As Variant
Dim i As Integer
‘define array:
arr = Array(“America”, “Europe”, “Africa”, “Asia”)
‘———————
‘JOIN
‘using the vba Join function to join substrings contained in an array:
varJoin = Join(arr, “&”)
‘return string after joining the substrings:
MsgBox varJoin
‘———————
‘CONCATENATE
‘concatenate each element of the array:
For i = 0 To UBound(arr)
‘Concatenate with &:
varConctnt = varConctnt & “&” & arr(i)
Next i
‘remove the “&” before the first element:
varConctnt = Mid(varConctnt, 2)
‘return string after concatenating with “&” – get the same effect as using Join function:
MsgBox varConctnt
End Sub
Example – Joining cell values within a defined worksheet range – using JOIN & CONCATENATE for the same affect. Refer Image 2.
To download Excel file with live code, click here.
Sub Join_Concatenate_2()
‘Joining cell values within a defined worksheet range – using JOIN & CONCATENATE for the same affect. Refer Image 2 for rng ie. ActiveSheet.Range(“A2:E4”).
Dim rng As Range, iRow As Integer, iCol As Integer, i As Integer
‘refer Image 2 for rng:
Set rng = ActiveSheet.Range(“A2:E4”)
‘———————
‘CONCATENATE
Dim varConctnt As Variant
For iRow = 1 To rng.Rows.Count
For iCol = 1 To rng.Columns.Count
If Not rng(iRow, iCol).Value = vbNullString Then
‘Concatenate with &:
varConctnt = varConctnt & “,” & rng(iRow, iCol).Value
End If
Next iCol
‘if array is empty:
If varConctnt = vbNullString Then MsgBox “Empty Array”: GoTo skip1
‘returns one string at a time in this order – “James Mitch,Dallas,44,Male,Maried” ; “Tom Halter,Tampa,28,Male,Unmarried” ; “Tracy White,Boston,35,Female,Married”
MsgBox Mid(varConctnt, 2)
varConctnt = “”
skip1:
Next iRow
‘———————
‘JOIN
‘declare a dynamic array:
Dim varArr() As Variant
iColumn = rng.Columns.Count
i = 0
‘resize the dynamic array (one-dimensional) that has already been declared, by using the ReDim statement:
ReDim varArr(iColumn – 1) As Variant
For iRow = 1 To rng.Rows.Count
For iColumn = 1 To rng.Columns.Count
If Not rng(iRow, iColumn).Value = vbNullString Then
‘for each vbNullString, decrease the array index value:
varArr(iColumn – 1 – i) = rng(iRow, iColumn).Value
Else
‘count number of vbNullString:
i = i + 1
‘if array is empty:
If i = rng.Columns.Count Then MsgBox “Empty Array”: GoTo skip2
End If
Next iColumn
‘decrease array size by number of vbNullString – ensure array values are not lost by using the ‘Preserve’ Keyword with the ReDim statement:
ReDim Preserve varArr(rng.Columns.Count – 1 – i) As Variant
‘using the Join function with comma as delimiter – returns one string at a time in this order – “James Mitch,Dallas,44,Male,Maried” ; “Tom Halter,Tampa,28,Male,Unmarried” ; “Tracy White,Boston,35,Female,Married”:
MsgBox Join(varArr, “,”)
skip2:
‘clear & resize array:
ReDim varArr(rng.Columns.Count – 1) As Variant
i = 0
Next iRow
End Sub
Example – Relating Split & Join functions – Split a string expression into an array of substrings; Join these substrings to return the original string expression, using the same Delimiter;
Sub Split_Join()
‘Relating Split & Join functions – Split a string expression into an array of substrings; Join these substrings to return the original string expression, using the same Delimiter;
Dim arr As Variant, varSplit As Variant, varDelim As Variant, varElements As Variant, varJoin As Variant
Dim i As Integer
‘specify web address:
varSplit = “http://www.excelsite.com/vba/userforms/20140215.htm”
‘specify delimiter:
varDelim = “/”
‘returns an array of 6 elements:
arr = Split(varSplit, varDelim)
‘return each element (second element is a zero-length string) of the array on a separate line:
For i = 0 To UBound(arr)
If i = 0 Then
‘not to give a line break before the first element:
varElements = arr(i)
Else
varElements = varElements & vbLf & arr(i)
End If
Next i
‘returns each element on a separate line:
MsgBox varElements
‘using the Join function returns the original string expression:
varJoin = Join(arr, varDelim)
‘returns – “http://www.excelsite.com/vba/userforms/20140215.htm”
MsgBox varJoin
End Sub
Example – Using string functions – Split, Join, Mid, Left, InStrRev, Concatenate – to extract substring after excluding nth element from a string.
Sub ExcludeElementExtractSubstring()
‘using string functions – Split, Join, Mid, Left, InStrRev, Concatenate – to extract substring after excluding nth element from a string.
Dim arr As Variant, varExp As Variant, varSubExp As Variant, varDelim As Variant, varJoin As Variant
Dim strFileName As String, strFolder As String
‘specify string expression from which you want to extract an element:
varExp = “Extract subexpression after excluding the nth element from an expression”
varDelim = ” “
‘using the Split function to return a zero-based, one-dimensional array:
arr = Split(varExp, varDelim)
‘——————————-
‘USING CONCATENATE / MID TO EXTRACT STRING AFTER EXCLUDING A SPECIFIC ELEMENT
‘to exclude a specific element, say the second element (“subexpression”), assign the element number (2) to a variable:
n = 2
For i = 0 To UBound(arr)
If i = n – 1 Then
varSubExp = varSubExp
Else
varSubExp = varSubExp & “,” & arr(i)
End If
Next i
‘remove the first “,”:
varSubExp = Mid(varSubExp, 2)
MsgBox varSubExp
‘——————————-
‘USING JOIN TO EXTRACT STRING AFTER EXCLUDING THE LAST ELEMENT
‘resize the dynamic array to reduce number of elements by 1 so as to exclude the last element (“expression”):
ReDim Preserve arr(UBound(arr) – 1)
‘join all array elements except the last, using the Join function, and adding dot at the end:
varJoin = Join(arr, varDelim) & “.”
‘return extracted string, excluding the last element:
MsgBox varJoin
‘——————————-
‘USING MID / INSTRREV / LEFT / LEN TO EXTRACT STRING AFTER EXCLUDING THE LAST ELEMENT
‘The above method of using the Join Function to exclude the last element is specifically useful to extract the folder path from a full file name, using “\” as the delimiter, viz. to extract “C:\Users\James\Documents\Excel\VBA\” from
“C:\Users\James\Documents\Excel\VBA\workbook.xls”
‘Below we show an alternate method to extract string excluding the last element ie. to extract the folder path from a full file name:
varExp = “C:\Users\James\Documents\Excel\VBA\workbook.xls”
‘extract the file name, from full file path (InStrRev returns position starting from the end of the string):
strFileName = Mid(varExp, InStrRev(varExp, “\”) + 1)
‘returns “workbook.xls”:
MsgBox strFileName
‘extract the file path (folder), excluding file name:
strFolder = Left(varExp, Len(varExp) – Len(strFileName))
‘returns “C:\Users\James\Documents\Excel\VBA\”:
MsgBox strFolder
End Sub