How To Manipulate Strings Using VBA Excel

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 etc. We will try to learn the following string operations/uses cases:

Appending Strings
Extracting a part of a string
Finding position in a string
Replacing a part of text in a string
Calculating length of string and reverse of string

Appending Strings

We can append the strings in VBA using & char as shown below:

String1 = “My name is: “

String2 = “Sam”

AppendedString = String1 & String2

So the AppendedString will result in: “My name is: Sam”

We can also do it like: AppendedString = “My name is: ” & “Sam” which will give the same result.

Extracting a part of a string

We use 3 functions to extract a string:

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

extract string

string extracted

Finding position in a string

We use the functions InStr or InStrRev:

Msgbox InStr(“Sam is my name”, “Sam”): Result: 1

It gives the starting position of the substring “Sam” in the main string “Sam is my name”,

Msgbox InStrRev (“Sam is my name”, “Sam”): Result: 1

It gives the starting position of the substring “Sam” in the main string “Sam is my name”, but the only difference is it will start searching the string from the end.

functions InStr or InStrRev

Replacing a part of text in a string

It can be done by using the function “Replace” as shown below:

Text1 = “Sam is my name”

Text2 = Replace(text1, “Sam”, “Ron”):

Result: “Ron is my name”

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

replacing string

string replaced

Calculating length of string and reverse of string

We can calculate the length of a string by using the function Len

Msgbox len(“Sam”) Result: 3

Msgbox len(“Sam 1”) Result: 5

It will count the number of characters in the string including the blanks.

We use the function StrReverse to reverse the string:

Msgbox StrReverse(“Sam”) result : “maS”

string reverse

string reversed