Excel VBA String Functions
VBA String Functions doest not replace the string but the result of this functions creates the new string. There are many string functions in VBA and they are all categorized under string or text functions, some of the important functions are LEFT function to get the value from left and RIGHT function to get the value from right or the MID function, LEN and INSTR function.
String functions are so important. Right from finding the number of characters of the supplied string we can extract any characters from the string. We can extract characters from the left side of the string, we can extract from the right side of the string, we can extract from the middle of the string, we can combine two texts, and we can split them at the same time as well.
It is important to have a fair bit of knowledge about all these VBA functions to them as part of the large project.
List of Top 6 String Functions in VBA
- LEN Function
- LEFT Function
- RIGHT Function
- MID Function
- TRIM Function
- Instr Function
One thing I would like to tell that “the VBA string functions are text functions in worksheet”.
We will discuss some of the important functions in this article.
#1 – LEN Function
LEN stands for “LENGTH”. This will give us the number of characters involved in the supplied string. For example, if you supply the word “Hello”, LEN function will return 5 as the result because there are 5 characters in the word “Hello”.
Below code will show the example.
Sub LEN_Example() Dim TotalCount As String TotalCount = Len("Hello") MsgBox TotalCount End Sub
This will show the result in the message box as 5.
#2 – LEFT Function
In order to extract the characters from the left side of the string, we need to use the LEFT function. Take a look at the syntax of the LEFT function.
- String is what the string we are trying to extract is.
- Length is nothing but how many characters you need from the left side of the supplied String.
Sub LEFT_Example() Dim FirstName As String FirstName = Left("Sachin Tendulkar", 6) MsgBox FirstName End Sub
This will extract the first 6 characters from the string “Sachin Tendulkar”. So the result will first name i.e. “Sachin”.
#3 – RIGHT Function
Like how we have extracted values from the left side of the string similarly we can extract from the right side of the string as well.
The syntax of the RIGHT function is exactly the same as the LEFT function.
- String is what the string we are trying to extract is.
- Length is nothing but how many characters you need from the right side of the supplied String.
Sub RIGHT_Example() Dim LastName As String LastName = Right("Sachin Tendulkar", 9) MsgBox LastName End Sub
This will extract 9 characters from the string “Sachin Tendulkar”. So the result will last name i.e. “Tendulkar”.
#4 – MID Function
Not only from the left and right side of the string but also from the middle of the string also we can extract the characters. Below is the syntax of the VBA MID function.
- String to Search: From which string we need the middle value
- Starting Position: What is the starting character position number to extract?
- Number of Characters to Extract: From the Starting Position how many characters to be extracted.
For example, if the name is “Sachin Ramesh Tendulkar” here middle name is “Ramesh” in this string starting position of the character to be extracted is 8 and we need 6 characters from the starting position. Below code will extract the middle value.
Sub MID_Example() Dim MiddleName As String MiddleName = Mid("Sachin Ramesh Tendulkar", 8, 6) MsgBox MiddleName End Sub
This will extract “Ramesh” from the middle of the string “Sachin Ramesh Tendulkar”.
#5 – TRIM Function
TRIM is the function to clean the data. It will eliminate unwanted space characters from the string. Take a look at the syntax of the TRIM VBA function.
It is straight forward what is the value or string you want to trim.
For an example assume you have the string “ Hello How are you?”. Here we have unnecessary space characters before the word “Hello” so by using TRIM we can eliminate this.
Sub TRIM_Example() Dim MyValue As String MyValue = Trim(" Hello How are you?") MsgBox MyValue End Sub
This will delete the unwanted space characters from the supplied string.
We have LTRIM and RTRIM functions as well in VBA. LTRIM will delete unwanted spaces from the left side of the string and RTRIM deletes unwanted spaces from the right side of the string.
#6 – Instr Function
Instr function is helpful in finding the position of the supplied character in the string. The syntax of the INSTR function is as follows.
- [Start] From which position of the supplied string we need the position.
- [String1] What is the string you are referring to?
- [String2] What is the character you are searching in the [String1].
For an example, if you have the word “Recipe” and you want to find the position of the character “e” from the first place below code will show the position of the letter “e”
Sub INSTR_Example() Dim MyValue As String MyValue = InStr(1, "Recipe", "e") MsgBox MyValue End Sub
So, from the first position of the string letter “e” position is 2.
If you want the position of the second appearance of the letter “e” then you need to use the Start argument as 3.
Sub INSTR_Example() Dim MyValue As String MyValue = InStr(3, "Recipe", "e") MsgBox MyValue End Sub
So, in this case, the position of the letter “e” after the first appearance is 6th.
These are some of the important string functions in VBA. Hope you have enjoyed it.
This has been a guide to VBA String Functions. Here we learn how to use list of top 6 VBA String Functions including LEN, LEFT, MID, Right, Instr and Trim along with examples and downloadable excel template. Below are some useful excel articles related to VBA –