VBA String Functions

The string is the most common data type used to store data in Excel. Therefore String functions become so important. Using these functions, you can manipulate string data, find the number of characters of the specified string value, combine two texts, split them simultaneously, extract only some characters from the string, or delete or trim the extra spaces present in the string value. We can trim values from the left side of the string.

In this tutorial, we will cover different functions that will permit you to extract substrings from strings from different positions, remove unwanted spaces from strings, split the string values, compare strings, and other useful string functions.

What are string Functions?

"VBA String Functions creates a new string by manipulating the data. It does not replace the original string but the output of this functions creates the new string. There are various string functions that are all categorized under VBA string or text functions."

7 Commonly used String Functions in VBA

Many string functions available in VBA enable the user to manipulate the string characters and work with text data in the code. Below given are the top 7 string functions that are commonly used in our day to day life while working with string data:

LEN Function
LEFT Function
RIGHT Function
MID Function
TRIM Function
Instr Function
Split Function

1 - LEN String Function

The VBA LEN function is an abbreviation for "LENGTH." This function returns the number of characters the user has supplied in the string. For instance, if we have supplied the string "Hello World", the LEN function will return 11 because, in total, there are 11 characters (including the space character) in the String "Hello World".

Syntax

Parameter

String (required): The String parameter represents the characters for which we want to find the total length.

Program: Using VBA Macro find the length of the string "Hello World".

Sub LEN_StringFunction_Example()
  'Declaring the variable
  Dim LenCount As String
  'Using Len function to count the total characters
  LenCount = Len("Hello World")
  MsgBox LenCount
End Sub

Output

Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output 11.

2 - LEFT String Function

The VBA LEFT Function is used to extract the length of characters from the left side of the specified string.

Syntax

Parameter

String (required): This parameter represents the length of the string we are trying to extract.
Length (required): This parameter represents but how many characters the user want from the left side of the given String.

Program: Write a VBA macro using the LEFT function to fetch the first name of the string "Reema Panda".

Sub LEFT_StringFunction_Example()
  'Declaring the string variable
  Dim FirstNme As String
  'Using the Left function to fetch the first name
  FirstNme = Left("Reema Panda", 5)
  'return the output
  MsgBox FirstNme

End Sub

Output

Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output.

The above code has extracted the first 5 characters from the string "Reema Panda." So the result will first name, i.e., "Reema".

3 - RIGHT String Function

In the above function, we have extracted values from the left side of the string; similarly, the VBA LEFT Function is used to extract the length of characters from the left side of the specified string.

Syntax

Parameter

String (required): This parameter represents the length of the string we are trying to extract from right side.
Length (required): This parameter represents but how many characters the user want from the right side of the given String.

Program: Write a VBA macro using the RIGHT function to fetch the first name of the string "Reema Panda".

Sub Right_StringFunction_Example()
  'Declaring the string variable
  Dim LastNme As String
  'Using the Right function to fetch the last name
  LastNme = Right("Reema Panda", 5)
  'return the output
  MsgBox LastNme
End Sub

Output

Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output.

This will extract 5 characters towards the right of the string "Reema Panda." Therefore, the output will last name, i.e., "Panda."

4 - MID String Function

The good thing about String functions is that not only do they allow the users to extract characters from the right or left of the string, but they also help to extract characters from the middle position as well.

For instance, if you have a String "Reema Devi Panda," here the middle value is "4" in this string starting position of the character to be extracted is 6, and we need 6 characters from the starting position.

Syntax

Parameter

String (Required): This parameter represents the length of the string we are trying to extract from the middle.
Starting Position (required): What is the starting character position number to extract?
Number of Characters to Extract (optional): From the Starting Position, how many characters to be extracted.

Program: Write a VBA program to extract the characters towards the middle of the string.

Sub MID_StringFunction_Example() 
 'Declaring the variable
  Dim MiddleNme As String
  'Using the Mid function to extract the middle name
  MiddleNme = Mid("Reema Rani Panda", 6, 6)
  'Fetching the output
  MsgBox MiddleNme
End Sub

Output

Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output.

As a result, it will extract the middle name "Rani" from the specified string.

5 - TRIM String Function

The VBA TRIM function is used for the cleaning of the data. It will eliminate unwanted space characters from the string. This function works straightforwardly and takes a string value (that you want to trim) as a parameter.

For example, assume you have the string " Hello, Do you love VBA? ". Here we have unnecessary space characters before the word "Hello," so by using TRIM. We can eliminate this

Syntax

Parameter

String (Required): This parameter represents the string from which you want to eliminate the unwanted space characters.

Program: Write a VBA program using the MID function to eliminate the unwanted space characters from the String string " Hello, Do you love VBA? "

Sub TRIM_StringFunction_Example()
  'Declaring the variable
  Dim MyVal As String
  'Using the Trim function to eliminate the unwanted space characters
  MyVal = Trim("   Hello, Do you love VBA?  ")
  'Fetching the output
  MsgBox MyVal

End Sub

Output

Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output.

As a result you will notice, all the extra space has been eliminated from the beginning and towards the end of the string.

NOTE: Unlike Right Left and Mid, VBA provides LTRIM and RTRIM functions as well in VBA. If you want to delete the undesired spaces from the left of the string use LTRIM function and to delete unwanted spaces from right side if the string suing the RTRIM function.

6 - Instr String Function

The VBA Instr String function is helpful in finding the position of any specific character in the supplied string.

Syntax

Parameter

Start (optional): This parameter represents the starting position for the search. If this parameter is not skipped, the default start value is 1.

String (require): This parameter represents the string to search within.

Substring (required): This parameter represents the substring that you want to find.

Compare (optional): This parameter represents the type of comparison to perform. It can be one of the following values:

VBA Constant	Value	Explanation
vbUseCompareOption	-1	Uses option compare
vbBinaryCompare	0	Binary comparison
vbTextCompare	1	Textual comparison

Program: Write a program to quickly find the position of 'o' in the string 'VBA World'.

Sub INSTR_StringFunction_Example()
  'Declaring the string variable
    Dim MyVal As String
  'Using the INSTR function to find position of 'o' in the string
     MyVal = InStr(1, "VBA World", "o")
  'return the output
    MsgBox MyVal
End Sub

Output

Run the code by pressing the F5 key and fetch the result. You will have the following Messagebox in your Excel sheet displaying the output.

As a result you will notice, the message box will display an output of 5.

7 - Split String Function

The SPLIT String function is used to split a string into substrings based on the supplied delimiter.

Syntax

Parameter

Expression (required): This parameter represents the input string that you want to split based on the delimiter.

Delimiter (optional): This parameter represents the delimiter used to split the string. It usually involves the 'Expression' argument.

Limit (optional): This parameter specifies the total number of substrings that the user want to return.

Compare (optional): This parameter return the type of comparison the user wish the SPLIT function to perform when evaluating the substrings.

Program

Sub Split_StringFunction_Example()
    'Decalaring a variable
    Dim TextStr As String
    Dim Result() As String
    TextStr = "Welcome to the world of VBA programming."
    'Spliting the string using the split function with delimeter space
    Result() = Split(TextStr)
End Sub

List of Other String Functions

S.NO	String Function	Explanation
1	String	The String function is used to convert numbers to a string.
2	Replace	The Replace string function replaces a substring in the specified string by another supplied substring.
3	StrReverse	The StrReverse String function return the specified string with the characters reversed.
4	Join	The Join Function is used join different strings.
5	InStrRev	The InStrRev function searches through strings for a substring
6	Format	The Format function is used to format a string based on the supplied instructions.
7	StrConv	The StrConv function converts the string to lower case or uppercase using the vbUpperCase or vbLowerCase argument.
8	UCase	The UCase function converts the string to upper case.
9	LCase	The LCase function converts the string to lower case.
10	CStr, Str	The Str or CStr function is used to convert numbers to a string.
11	StrComp	The StrComp is used to compare two strings.
12	Like	The Like function checks if string has the supplied pattern