How to Manipulate Strings in Excel Vba

In this article, we will learn to do string operations using Excel VBA. String can be any text or a combination of text and numbers. VBA offers several functions to perform string operations:

Appending Strings

Use the & character to append strings:

String1 = "My name is: "
String2 = "Sam"
AppendedString = String1 & String2

This will result in the string “My name is: Sam”.

Extracting a part of a string

Use 3 functions to extract a string:

1. Left: result is left 3 chars: “Sam”
2. Right: Right(“Sam is my name”, 4) result is left 4 chars: “name”
3. Mid: Mid(“Sam is my name”, 1, 3) result is Mid 3 chars starting position is 1: “Sam”

Finding position in a string

Use the InStr or InStrRev functions to find the position of a substring in a string:

• InStr: Searches for a substring in a string from the beginning.

MsgBox InStr("Sam is my name", "Sam")

This will return the number 1, which is the starting position of the substring “Sam” in the main string “Sam is my name”.

• InStrRev: Searches for a substring in a string from the end.

MsgBox InStrRev("Sam is my name", "Sam")

This will also return the number 1.

Replacing a part of text in a string

Use the Replace function to replace a part of text in a string:

Text1 = "Sam is my name"
Text2 = Replace(text1, "Sam", "Ron")

The replace function will replace the word “Sam” in the string text1 by the word “Ron”.

Calculating length of string and reverse of string

Use the Len and StrReverse functions to calculate the length of a string and reverse it, respectively:

• Len: Returns the length of a string, including spaces.

MsgBox Len("Sam")

This will return the number 3.

• StrReverse: Reverses the order of the characters in a string.

MsgBox StrReverse("Sam")

This will return the string “maS”.