VBA String Functions

VBA String Functions. Excel users typically store information as strings. As a result, string functions have become highly significant. You may find the number of characters in a given string value, join or divide two strings at once, extract a subset of characters from a string, or remove whitespace using these functions. The left-side string values can be trimmed.

6 String Functions in VBA

Several string functions in VBA let the user change the characters in a string and work with text data in the code. Here are the top six string functions that we frequently utilise while dealing with string information in our daily lives:

1. LEN String Function

The VBA LEN function is an acronym for “LENGTH.” The function tells the user how many characters are in the string.

Syntax

Len(String)

Program: Find out how long the string “Hello World” is by using VBA Macro.

Sub LEN_String_Function_Example()

Dim str_LenCount As String

str_LenCount = Len("Hello World")

MsgBox str_LenCount

End Sub

Output

Hit the F5 key to execute the code and get the result. You will get the Msgbox in your Excel sheet showing output 11.

2. LEFT String Function

The VBA LEFT Function helps derive the characters’ length from the left side of the specified string.

See More: VBA String Operator

Syntax

Left (String, Length)

Program: Write a VBA macro that uses the LEFT function to get “Robin Simon” as the first name from the string.

Sub Left_String_Function_Example()

Dim str_FirstName As String

str_FirstName = Left("Robin Simon", 5)

MsgBox str_FirstName

End Sub

Output

Press the F5 key to execute the code and get the result.

3. RIGHT String Function

Values can be taken from the right side of the string, similar to the left side.

Syntax

RIGHT (String, Length)

Program: Write a VBA macro that uses the RIGHT function to get “Robin Simon” as the first name from the string.

Sub Right_String_Function_Example()

Dim str_LastName As String

str_LastName = Right("Robin Simon", 5)

MsgBox str_LastName

End Sub

Output

Tap the F5 key to run the code and get the result.

4. MID String Function

String functions are helpful. They let the users obtain characters from the right or left side of the string and characters from the middle.

Syntax

MID (String, Start As Long, [Length])

Program: Write a VBA programme to get the characters in the middle of the string.

Sub Mid_String_Function_Example()

Dim str_MiddleName As String

str_MiddleName = Mid("Robin Mark Simon", 6, 5)

MsgBox str_MiddleName

End Sub

Output

Enter the F5 key to run the code and obtain the result.

5. TRIM String Function

The TRIM function in VBA helps clean up data. It will eliminate any space characters that don’t belong in the string. This function is easy to use. It takes as a parameter a string value that you want to trim.

Syntax

TRIM (String)

Program: Write a VBA programme with the MID function to remove the extra spaces from the string “Hello, Do you love VBA?”

Sub Trim_String_Function_Example()

Dim str_Value As String

'The Trim function will eliminate the unwanted space characters from the text

str_Value = Trim(" Hello World")

MsgBox str_Value

End Sub

Output

Hit the F5 key to execute the code and obtain the result.

6. Instr String Function

The VBA Instr String function can help you find where a specific character is in the string you give it.

Syntax

InStr( [start], string, substring, [compare] )

Program: Write a programme that finds the letter ‘W’ in the string ‘Hello World’.

Sub Instr_String_Function_Example()

Dim str_Value As String

'The INSTR function to find position of 'W' in the string

str_Value = InStr(1, "Hello World", "W")

MsgBox str_Value

End Sub

Output

Press the F5 key to execute the code and obtain the result.