How to Manipulate Strings in Excel Vba

In this article, we will learn to perform string operations using Excel VBA. A 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

You can extract a portion of a string using the Left, Right, or Mid functions. The Left function retrieves a specified number of characters from the start of 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

To find the position of a substring within a string, use the InStr or InStrRev functions. The InStr function searches 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”.

The InStrRev function searches 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

To find the length of a string, use the Len function:

MsgBox Len("Sam")

This will return the number 3.

To reverse a string, use the StrReverse function:

MsgBox StrReverse("Sam")

This will return the string “maS”.

I hope that thanks to this tutorial you will efficiently manipulate strings in Excel VBA.