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:

  1. LEN Function
  2. LEFT Function
  3. RIGHT Function
  4. MID Function
  5. TRIM Function
  6. Instr Function
  7. 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”.

VBA String Functions

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.

VBA String Functions

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”.

VBA String Functions

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.

VBA String Functions

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”.

VBA String Functions

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.

VBA String Functions

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.

VBA String Functions

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.

VBA String Functions

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? “

VBA String Functions

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.

VBA String Functions

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 ConstantValueExplanation
vbUseCompareOption-1Uses option compare
vbBinaryCompare0Binary comparison
vbTextCompare1Textual comparison

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

VBA String Functions

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.

VBA String Functions

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

VBA String Functions

List of Other String Functions

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

Next Topic#