VBA String Functions

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA String Functions

VBA String functions do not replace the string, but the result of these functions creates a new string. There are many string functions in VBA. They are all categorized under String or Text functions. Some important functions are the LEFT function to get the value from the left and the RIGHT function to get the value from the right or the MID function, LEN, and INSTR function.

String functions are so important. We can extract any characters from the string by finding the number of characters of the supplied 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 functionsVBA FunctionsVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more as part of the large project.

List of Top 6 String Functions in VBA

  1. LEN Function
  2. LEFT Function
  3. RIGHT Function
  4. MID Function
  5. TRIM Function
  6. Instr Function
VBA String Functions

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA String Functions (wallstreetmojo.com)

One thing we would like to say is that “the VBA string functions are text functions in the worksheet.”

You must have already used LEN, LEFT, RIGHT, MID, and SUBSTITUTE excel functionsSUBSTITUTE Excel FunctionsSubstitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.read more to play around with the data. In VBA, too, we can use them to play around with the data.

We will discuss some of the important functions of this article.

You can download this VBA String Functions Excel Template here – VBA String Functions Excel Template

#1 – LEN Function

LEN stands for “LENGTH.” Therefore, it will give us the number of characters involved in the supplied string. For example, if you supply the word “Hello,” LEN in the excelLEN In ExcelThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more function will return 5 because there are five characters in the word “Hello.”

The below code will show the example.

Code:

Sub LEN_Example()

  Dim TotalCount As String

  TotalCount = Len("Hello")

  MsgBox TotalCount

End Sub

It will show the result in the message box as 5.

VBA LEN Example

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

#2 – LEFT Function

We need to use the VBA LEFTVBA LEFTExcel VBA Left is an inbuilt worksheet text function that facilitates the user to extract a certain number of characters (denoted as N) from the left side of a string. It helps in pulling the leftmost substring from a particular string in the data set.read more function to extract the characters from the left side of the string. First, take a look at the syntax of the LEFT function.

LEFT Formula
  • The 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.

Code:

Sub LEFT_Example()

  Dim FirstName As String

  FirstName = Left("Sachin Tendulkar", 6)

  MsgBox FirstName

End Sub

It will extract the first six characters from the string “Sachin Tendulkar.” So, the result will be the first name,  “Sachin.”

VBA LEFT Example

#3 – RIGHT Function

Like how we have extracted values from the left side of the string similarly, we can also extract from the right side of the string.

The syntax of the RIGHT functionSyntax Of The RIGHT FunctionRight function is a text function which gives the number of characters from the end from the string which is from right to left. For example, if we use this function as =RIGHT ( “ANAND”,2) this will give us ND as the result.read more is the same as the LEFT function.

Right Formula
  • The 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.

Code:

Sub RIGHT_Example()

  Dim LastName As String

  LastName = Right("Sachin Tendulkar", 9)

  MsgBox LastName

End Sub

It will extract 9 characters from the string “Sachin Tendulkar.” So, the result will be the last name “Tendulkar.”

VBA Right Example

#4 – MID Function

We can extract the characters from the left and right sides and the middle of the string. Below is the syntax of the VBA MID functionVBA MID FunctionVBA MID function extracts the middle values from the supplied sentence or word. It is categorized under the String and Text function and is a worksheet function.read more.

MID formula
  • String to Search: From which string do we need the middle value?
  • Starting Position: What is the starting character position number to extract?
  • Number of Characters to Extract: How many characters need to be extracted from the Starting Position?

For example, if the name is “Sachin Ramesh Tendulkar,” the middle name is “Ramesh” in this string starting position of the character to be extracted 8. Therefore, we need six characters from the starting position. The below code will extract the middle value.

Code:

Sub MID_Example()

  Dim MiddleName As String

  MiddleName = Mid("Sachin Ramesh Tendulkar", 8, 6)

  MsgBox MiddleName

End Sub

It will extract “Ramesh” from the middle of the string “Sachin Ramesh Tendulkar.”

VBA String Functions Example

#5 – TRIM Function

TRIM is the function of cleaning the data. It will eliminate unwanted space characters from the string. First, take a look at the syntax of the TRIM function.

Trim formula

It is straightforward what value or string you want to trim.

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

Code:

Sub TRIM_Example()

  Dim MyValue As String

  MyValue = Trim(" Hello How are you?")

  MsgBox MyValue

End Sub

It will delete the unwanted space characters from the supplied string.

VBA String Functions Example

We have LTRIM and RTRIM functions as well in VBA. For example, LTRIM will delete unwanted spaces from the left side of the string, and RTRIM deletes unwanted spaces from the right side.

#6 – Instr Function

The Instr function helps find the position of the supplied character in the string. The syntax of the INSTR function is as follows:

Instr Formula
  • [Start] From which position of the supplied string do we need the position?
  • [String1] What is the string you are referring to?
  • [String2] What character are you searching for in [String1]?

For example, if you have the word “Recipe” and you want to find the position of the character “e” from the first place below, the code will show the position of the letter “e.”

Code:

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.

VBA String Functions Example

If you want the position of the second appearance of the letter “e,” then you need to use the Start argument as 3.

Code:

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.

VBA String Functions Example 1

These are some of the important String functions. We hope you have enjoyed it.

Recommended Articles

This article has been a guide to VBA String Functions. Here, we learn a list of the top 6 VBA String functions, including LEN, LEFT, MID, Right, Instr, and Trim, along with examples and a downloadable Excel template. Below are some useful Excel articles related to VBA: –