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”.
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”.
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”.
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.
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? “
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’.
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
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 |