Site icon Best Excel Tutorial

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:

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

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:

MsgBox Len("Sam")

This will return the number 3.

MsgBox StrReverse("Sam")

This will return the string “maS”.

Exit mobile version