VBA Strings and Substrings Functions

Associated Files Download Links

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:

The result delivered is:

How to Use the Left String Function in VBA

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:

The result delivered is:

Using the Right String Function in VBA

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:

The result delivered is:

Using the Mid String Function in VBA

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:

The result delivered is:

Using the Instr Function in VBA

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:

The result delivered is:

Using The InstrRev Function in VBA

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:

The results delivered are:

String With Leading Spaces

Using the LTrim String Function To Remove Leading Spaces

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:

The results delivered are:
Message box With Trailing Spaces

Using The RTrim String Function

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:

The results delivered are:
Message box With Leading And Trailing Spaces

Using The Trim Function in VBA

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:

The result delivered is:

Using The LCase Function in VBA

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:

The result delivered is:

Using The UCase Function in VBA

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:

The result delivered is:

Using The StrConv Function in VBA

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.  If you want to learn more about the StrComp Function click here. 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:

The result delivered is:

Using The StrComp Function in VBA

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:

The result delivered is:

Using The Like Operator in VBA

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:

The result delivered is:
Using The Like Operator To Match Patterns in VBA

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:

The result delivered is:

Using The Replace String Function in VBA

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:

The result delivered is:

Using The StrReverse Function in VBA

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:

The result delivered is:

Using The Len String Function in VBA

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

 

 

You may also like some of this related content...

In VBA, you can compare two strings using the StrComp function. In this tutorial, you
Reverse a String of Text with VBA The VBA Routine below allows the user to
INSTR Function The VBA Instr Function checks if a string of text is found in
Return nth Word in String of Text The following function will find the nth word
If you always know the length of a string, it's easy to remove characters from
Advertisements
Automate Excel
Left Menu Icon