VBA Strings and Substrings Functions

VBA has many string functions that will allow you to manipulate and work with text and strings in your code. In this tutorial, we are going to cover functions that will allow you to extract substrings from strings, remove spaces from strings, convert the case of a text or string, compare strings and other useful string functions.

Extracting a Substring

The VBA Left String Function

The VBA Left Function allows you to extract a substring from a text or string starting from the left side. The syntax of the VBA Left String Function is:

Left(String, Num_of_characters) where:

• String – The original text.
• Num_of_characters  – An integer that specifies the number of characters to extract from the original text starting from the beginning.

The following code shows you how to use the Left String Function to extract the first four characters of the given string:

Sub UsingTheLeftStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "AutomateExcel"
valueTwo = Left(valueOne, 4)

MsgBox valueTwo

End Sub

The result is:

The Left Function has extracted the first four letters of AutomateExcel, which are Auto.

The VBA Right String Function

The VBA Right Function allows you to extract a substring from a text or string starting from the right side. The syntax of the VBA Right String Function is:

Right(String, Num_of_characters) where:

• String – The original text.
• Num_of_characters  – An integer that specifies the number of characters to extract from the original text starting from the ending.

The following code shows you how to use the Right String Function to extract the last four characters of the string:

Sub UsingTheRightStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "AutomateExcel"
valueTwo = Right(valueOne, 4)

MsgBox valueTwo

End Sub

The result is:

The Right Function has extracted the last four letters of AutomateExcel, which are xcel.

The VBA Mid String Function

The VBA Mid Function allows you to extract a substring from a text or string, starting from any position within the string that you specify. The syntax of the VBA Mid String Function is:

Mid(String, Starting_position, [Num_of_characters]) where:

• String – The original text.
• Starting_position – The position in the original text, where the function will begin to extract from.
• Num_of_characters (Optional) – An integer that specifies the number of characters to extract from the original text beginning from the Starting_position. If blank, the MID Function will return all the characters from the Starting_position.

The following code shows you how to use the Mid String Function to extract four characters, starting from the second position or character in the string:

Sub UsingTheMidStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "AutomateExcel"
valueTwo = Mid(valueOne, 2, 4)

MsgBox valueTwo

End Sub

The result is outputted to a msgbox:

The Mid Function has extracted the four letters of AutomateExcel starting from the second character/position/letter which are utom.

Finding the Position of a Substring

The VBA Instr String Function

The VBA Instr Function returns the starting position of a substring within another string. This function is case-sensitive. The syntax of the VBA Instr String Function is:

Instr([Start], String, Substring, [Compare]) where:

• Start (Optional) – This specifies the starting position for the function to search from. If blank, the default value of 1 is used.
• String – The original text.
• Substring– The substring within the original text that you want to find the position of.
• Compare (Optional) – This specifies the type of comparison to make. If blank, binary comparison is used.

-vbBinaryCompare – Binary comparison (Upper and lower case are regarded as different)
-vbTextCompare – Text comparison (Upper and lower case are regarded as the same)
-vbDatabaseCompare – Database comparison (This option is used in Microsoft Access only, and is a comparison based on the database)

The following code shows you how to use the Instr String Function to determine the first occurrence of the substring “Th” within the main string:

Sub UsingTheInstrStringFunction()

Dim valueOne As String
Dim positionofSubstring As Integer

valueOne = "This is The Text "
positionofSubstring = InStr(1, valueOne, "Th")

Debug.Print positionofSubstring


End Sub

The result (outputted to the Immediate Window) is:

The Instr Function has returned the position of the first occurrence of the substring “Th” which is 1. Note this function includes the spaces in the count.

The VBA InstrRev String Function

The VBA InstrRev Function returns the starting position of a substring within another string but it starts counting the position, from the end of the string. This function is case-sensitive. The syntax of the VBA InstrRev String Function is:

InstrRev(String, Substring, [Start], [Compare]) where:

• String – The original text.
• Substring – The substring within the original text that you want to find the position of.
• Start (Optional) – This specifies the position to start searching from. If blank, the function starts searching from the last character.
• Compare (Optional) – This specifies the type of comparison to make. If blank, binary comparison is used.

-vbBinaryCompare – Binary comparison (Upper and lower case are regarded as different)
-vbTextCompare – Text comparison (Upper and lower case are regarded as the same)
-vbDatabaseCompare – Database comparison (This option is used in Microsoft Access only, and is a comparison based on the database)

The following code shows you how to use the InstrRev String Function to determine the first occurrence of the substring “Th” within the main string, starting from the end of the string:

Sub UsingTheInstrRevStringFunction()

Dim valueOne As String
Dim positionofSubstring As Integer

valueOne = "This is The Text "
positionofSubstring = InStrRev(valueOne, "Th")

Debug.Print positionofSubstring

End Sub

The result is outputted to the Immediate Window:

The InstrRev Function has returned the position of the first occurrence of the substring “Th”, but starting the counting from the end which is 9. Note this function includes the spaces in the count.

Removing Spaces from a String

The VBA LTrim String Function

The VBA LTrim Function removes all the leading spaces from a text or string. The syntax of the VBA LTrim String Function is:

LTrim(String) where:

• String – The original text.

The following code shows you how to use the VBA LTrim Function to remove the leading spaces in the given string:

Sub UsingTheLTrimStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "         This is the website adddress https://www.automateexcel.com/excel/"
valueTwo = LTrim(valueOne)

MsgBox valueOne
MsgBox valueTwo

End Sub

The results are:

The LTrim Function has removed the leading spaces for valuetwo, which is shown in the second Message Box.

The VBA RTrim String Function

The VBA RTrim Function removes all the trailing spaces from a text or string. The syntax of the VBA RTrim String Function is:

RTrim(String) where:

• String – The original text.

The following code shows you how to use the VBA RTrim Function to remove the trailing spaces in the given string:

Sub UsingTheRTrimStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "This is the website adddress https://www.automateexcel.com/excel/               "
valueTwo = RTrim(valueOne)

MsgBox valueOne
MsgBox valueTwo

End Sub

The results delivered are:

The RTrim Function has removed the trailing spaces for valuetwo, which is shown in the second Message Box.

The VBA Trim String Function

The VBA Trim Function removes all leading and trailing spaces from a text or string. The syntax of the VBA Trim String Function is:

Trim(String) where:

• String – The original text.

The following code shows you how to use the VBA Trim Function to remove the leading and trailing spaces in the given string:

Sub UsingTheTrimStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "           This is the website adddress https://www.automateexcel.com/excel/             "
valueTwo = Trim(valueOne)

MsgBox valueOne
MsgBox valueTwo

End Sub

The results are:

The Trim Function has removed the leading and trailing spaces for valuetwo, which is shown in the second Message Box.

VBA Case Functions

The VBA LCase String Function

The VBA LCase Function converts letters in a text or string to lower case. The syntax of the VBA LCase String Function is:

LCase(String) where:

• String – The original text.

The following code shows you how to use the LCase String Function to convert all the letters in the given string to lower case:

Sub UsingTheLCaseStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "THIS IS THE PRODUCT"
valueTwo = LCase(valueOne)

MsgBox valueTwo

End Sub

The result is:

The LCase Function has converted all the letters in the string to lower case.

The VBA UCase String Function

The VBA UCase Function converts letters in a text or string to upper case. The syntax of the VBA UCase String Function is:

UCase(String) where:

• String – The original text.

The following code shows you how to use the UCase String Function to convert all the letters in the given string to upper case:

Sub UsingTheUCaseStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "this is the product"
valueTwo = UCase(valueOne)

MsgBox valueTwo

End Sub

The result is:

The UCase Function has converted all the letters in the string to upper case.

The VBA StrConv Function

The VBA StrConv Function can convert letters in a text or string to upper case, lower case, proper case or unicode depending on type of conversion you specify.  The syntax of the VBA StrConv String Function is:

StrConv(String, Conversion, [LCID]) where:

• String – The original text.
• Conversion – The type of conversion that you want.
• [LCID] (Optional) – An optional parameter that specifies the LocaleID. If blank, the system LocaleID is used.

The following code shows you how to use the StrConv String Function to convert the string to proper case:

Sub UsingTheStrConvStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "this is THE product"
valueTwo = StrConv(valueOne, vbProperCase)

MsgBox valueTwo

End Sub

The result is:

You specify the type of conversion you want to perform using the conversion parameter:

• vbLowerCase converts all the letters in the text to lower case.
• vbUpperCase converts all the letters in the text to upper case.
• vbProperCase converts the first letter of each word in the text to upper case, while all the other letters are kept as lower case.
• vbUnicode converts a string to unicode.
• vbFromUnicode converts a string from unicode to the default code page of the system.

Comparing Strings

The VBA StrComp Function

The VBA StrComp String Function allows you to compare two strings. The function returns:

• 0 if the two strings match
• -1 if string1 is less than string2
• 1 if string1 is greater than string2
• A null value if either of the strings was Null

The following code shows you how to use the StrComp Function to compare two strings:

Sub UsingTheStrCompStringFunction()

Dim valueOne As String
Dim valueTwo As String
Dim resultofComparison As Integer

valueOne = "AutomateExcel"
valueTwo = "AutomateExcel"
resultofComparison = StrComp(valueOne, valueTwo)
Debug.Print resultofComparison

End Sub

The result is:

The StrComp Function has found an exact match between the two strings and returned 0.

The VBA Like Operator

The VBA Like Operator allows you to compare a text or string to a pattern and see if there is a match. You would usually use the Like Operator in conjunction with wildcards. The following code shows you how to use the Like Operator:

Sub UsingTheLikeOperatorInVBA()

Dim valueOne As String
valueOne = "Let's view the output"

If valueOne Like "*view*" Then
MsgBox "There is a match, this string contains the word view"
Else
MsgBox "No match was found"
End If

End Sub

The result is:

The wildcards you can use with the Like Operator to find pattern matches include:

• ? which matches a single character
• # which matches a single digit
• * which matches zero or more characters

The following code shows you how you would use the Like Operator and the ? wildcard to match a pattern in your code:

Sub UsingTheLikeOperatorWithAWildcardInVBA()

Dim valueOne As String
valueOne = "The"

If valueOne Like "??e" Then
MsgBox "There is a match, a matching pattern was found"
Else
MsgBox "No match was found"
End If

End Sub

The result delivered is:

Other Useful VBA String Functions

The VBA Replace String Function

The VBA Replace Function replaces a set of characters in a string with another set of characters. The syntax of the VBA Replace String Function is:

Replace(String, Find, Replace, [Start], [Count], [Compare]) where:

• String – The original text.
• Find – The substring to search for within the original text.
• Replace – The substring to replace the Find substring with.
• Start (Optional) – The position to begin searching from within the original text. If blank, the value of 1 is used and the function starts at the first character position.
• Count (Optional) – The number of occurrences of the Find substring in the original text to replace. If blank, all the occurrences of the Find substring are replaced.
• Compare (Optional) – This specifies the type of comparison to make. If blank, binary comparison is used.

  -vbBinaryCompare – Binary comparison
  -vbTextCompare – Text comparison
  -vbDatabaseCompare – Database comparison (This option is used in Microsoft Access only, and is a comparison based on the database.)

The following code shows you how to use the Replace String Function:

Sub UsingTheReplaceStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "ProductABC"
valueTwo = Replace(valueOne, "ABC", "XYZ")

MsgBox valueTwo

End Sub

The result is:

The Replace Function found the substring ABC within ProductABC and replaced it with the substring XYZ.

The VBA StrReverse Function

The VBA StrReverse Function reverses the characters in a given text or string. The syntax of the VBA StrReverse String Function is:

StrReverse(String) where:

• String – The original text.

The following code shows you how to use the VBA StrReverse Function to reverse the characters in the string Product:

Sub UsingTheStrReverseStringFunction()

Dim valueOne As String
Dim valueTwo As String

valueOne = "Product"
valueTwo = StrReverse(valueOne)

MsgBox valueTwo

End Sub

The result is:

The VBA Len String Function

The VBA Len Function returns the number of characters in a text string. The syntax of the VBA Len String Function is:

Len(String) where:

• String – The original text.

The following code shows you how to use the Len String Function to determine the length of the string AutomateExcel:

Sub UsingTheLenFunction()

Dim valueOne As String
Dim stringLength As Integer

valueOne = "AutomateExcel"
stringLength = Len(valueOne)
Debug.Print stringLength


End Sub

The result is:

The Len Function has counted all the characters in the text AutomateExcel, which is 13 letters.