VBA String Functions

What are Excel VBA String Functions?

String functions are an essential feature of VBA. VBA String functions recreate a new string by data manipulation. The original string is not replaced. Instead, a new one is created. Several string functions are available under VBA Strings for data manipulation.

We can use the VBA Strings functions in several ways. For example, we can trim the characters from the left or right side, combine two texts, delete, or remove extra space from text, find the number of characters in the specified string, etc., and print them in a message box. To understand and access VBA String functions, let us see a simple example. Here, we will use the “TRIM” String function. This function eliminates/trims the characters specified.

Step 1: Open VBA in Excel, click the Insert tab, and choose Module. It will open a new module, as shown below.

The syntax of the “TRIM” function is as follows:

VBA String Function Intro - Step 1 - Syntax

Step 2: Begin to write a sub-procedure as shown below.

Step 3: Now, for writing the code in VBA, define MyRes as a String. It will allow us to use characters or text in our command line.

Step 4: Any character can be stored in this variable. For Example: Suppose we have a String “ Hello, Do you understand VBA Strings.” Here, we wish to eliminate the unwanted space before Hello. To see this text in a message box, we will use the MsgBox function.

Code:

Sub Trim_StringExample()
Dim MyRes As String
MyRes = Trim(“ Hello,Do you understand VBA Strings”)
MsgBox MyRes
End Sub

Step 5: Now, run the VBA code using F5 and you will see a message box showing “Hello, Do you understand VBA Strings” in Excel.

What are Excel VBA String Functions?

VBA String functions are one of the simplest features of VBA. Many different forms of texts and characters can be printed using VBA String functions.
Ensure the use of inverted commas when writing the string for the String function.
If we supply a negative number to the VBA string functions wherever numerical arguments are required, we get a run-time 5 error.
Similarly, if the “Character” argument is Null in VBA String functions, then VBA will return a Null.

Let us look at the other VBA String functions.

List of Top 6 String Functions in VBA

#1 – The LEN Function

The VBA LEN Function is an Acronym for “Length”. This function can be used to obtain the number of characters is a specified string. For example, if we have the string “Good Morning,” the LEN excel Function will return 12 as the number of characters (including space as one character).

The syntax for LEN:

Please check the example of the below code:

Code:

Sub LEN_StringExample()
Dim MyRes As String
MyRes = Len(“Good Morning”)
MsgBox MyRes
End Sub

The result will be displayed in the message box as:

#2 – The LEFT Function

This function is used to extract characters from the left side of the specified string. The syntax is shown below:

String: The string is the text used for the extraction.
Length specifies the number of characters we need to extract from the left side of the specified string.

For example, if we specify “Thomas Edison” as the argument and use the LEFT excel Function to extract six characters from the left side, the output will be “Thomas” in the message box.

Code:

Sub LEFT_StringExample()
Dim MyRes As String
MyRes = Left(“Thomas Edison”, 6)
MsgBox MyRes
End Sub

Run the code and see the result in the Excel message box.

#3 – The RIGHT Function

In the above example, we saw how to extract values from the left side of a string; similarly, the RIGHT Function is used to extract the characters from the right side of the string.

The syntax is shown below

VBA String Function - Right - syntax.jpg

String: The string is the text from which we must extract.
Length is the number of characters we must extract from the right side of the specified string.

For example, if we have specified the name “Albert Einstein” as the string and use the RIGHT Function to extract eight characters from the right side, the output will be “Einstein” in the message box.

Code:

Sub RIGHT_StringExample()
Dim MyRes As String
MyRes = Right(“Albert Einstein”, 8)
MsgBox MyRes
End Sub

Run the code and see the result in the Excel message box as:

#4 – The MID Function

Characters can be extracted from the middle of the text using this function. The syntax is shown below:

String: The string is the text from which we must extract the value
Start: To find the starting character’s position for extraction.
[Length]: (Optional) The number of characters to extract from the starting position

For example, if we have specified the name “Thomas Alwa Edison” in the string, we can use the MID excel function to extract six characters, starting from position 7. The output will be “ Alwa “ in the message box. Note that the spaces at the beginning and end of the word Alwa will also be counted as a part of the extraction.

Code:

Sub MID_StringExample()
Dim MyRes As String
MyRes = Mid(“Thomas Alwa Edison”, 7, 6)
MsgBox MyRes
End Sub

To see the result in Excel, run the code.

#5 – The TRIM Function

This function is used to eliminate unwanted apace characters from the specified String.

The syntax is shown below:

For example, we have specified “ Welcome to the Jurassic Park ” in the String, with spaces before and after the text. We can use the TRIM Excel Function to eliminate unwanted spaces at both ends. The output will be “Welcome to the Jurassic Park” in the message box.

Code:

Sub Trim_StringExample()
Dim MyRes As String
MyRes = Trim(“ Welcome to the Jurassic Park “)
MsgBox MyRes
End Sub

Run the code to obtain the result of this function.

If you use the LEN function after TRIM, you can clearly understand how it works. Insert the following line of code at the end of the sub-procedure:

Sub Trim_StringExample()
Dim MyRes As String
MyRes = Trim(“ Welcome to the Jurassic Park “)
MsgBox MyRes
MsgBox Len(MyRes)
End Sub

Now, if you run the code, you get the following output.

The result is the string length of “Welcome to the Jurassic Park” after trimming the leading and trailing spaces.

#6 – The Instr Function

This VBA function is used to find the position of any specified character in a supplied String.

The syntax is shown below:

[Start]: To know the starting position of the supplied string.
[String1]: The string we are referring to
[String2] The character we are searching for in [String1]

In this example, we have specified the text “Park” in the String and will use Instr Function to find the position of the specified character “a.” The output will be “2” in the message box as that is the position of the character “a.” Here, the search begins from the first position as we have specified “1” as the first argument.

Code:

Sub Instr_StringExample()
Dim MyRes As String
MyRes = InStr(1, “Park”, “a”)
MsgBox MyRes
End Sub

To see the result, run the code.

Important Things to Remember

You should follow the syntax carefully to get an accurate result from the VBA String functions.
Apart from the six essential VBA String functions specified above, the VBA String Functions Replace replaces a substring within another VBA string functions substring.
Another useful string function in VBA is the “Split Function.” Using the Split string function, we can split strings into multiple substrings based on a defined delimiter.

Frequently Asked Questions (FAQs)

1. How do you manipulate a String in VBA?

In Excel, you can find several types of functions to manipulate a String in VBA. These include:

• Left: is available to extract characters from the left side of a string.
• Right: To extract characters from the right side of a string.
• Join: is used to combine two strings.
• Mid: To extract a substring from the middle of a string
• Len: For finding the length of a string.
• Instr: To find the position of a substring in a string.

2. Which function in VBA reverse given String?

To reverse a String in VBA, use the StrReverse function. It returns the reverse of the original specified string. For example: if we supply the string “Human,” it will return “namuH” in the result.

3. Can you compare Strings in VBA?

Yes, we can compare Strings in VBA by using a function available as “STRCOMP.” This function compares two strings in VBA. It is useful when we try to compare case-sensitive words. For Example, “VBA” is not equal to “vba”.

4. How do I convert a String to a double in VBA?

String and Double are the two crucial functions under VBA String functions. A few methods available to convert String into Double are listed below:

• MsgBox is used to convert String to Double
• By using VBA Sum Function.
• Through VBA Text Function.
• By creating a function to convert String to Double.
• By converting a range of Strings to Double.