Excel VBA String Manipulation

String Manipulation is another important element of MS Excel and is widely used in Macro building. In this tutorial you will observe the implementation of some of the most commonly used string manipulation techniques.

For an effective learning through this tutorial, you will need to place a Command Button on MS Excel worksheet and will apply following line of codes under the Click event of the command button.

Join Strings

Join string is used to join or concatenate multiple words or phrases to form a single phrase. The implementation example of Join is the joining of First Name and Last Name to form a Full Name.

Try following code for better understanding:

Dim FirstName as String

Dim LastName as String

FirstName = “John”

LastName = “Smith”

Msgbox FirstName & “ “ & LastName

Result would be:

The above implementation reflects the joining of 3 string objects, FirstName, LastName and “ “ (Space) between them.

Left

Left function is used to extract the specific length of value from the left side of a given string.

Try following code for better understanding:

Dim strText As String
strText = “Welcome to MS Excel Tutorial”

Msgbox Left(strText, 6)

Result:

Right

Right function is used to extract the specific length of value from the right side of a given string.

Try following code for better understanding:

Dim strText As String
strText = “Welcome to MS Excel Tutorial”

Msgbox Right(strText, 6)

Result:

Mid

Mid function is used to extract a substring from a main string based on the given starting point and length of substring.

Try following code for better understanding:

Dim strText As String
strText = “Welcome to MS Excel Tutorial”

Msgbox Mid(strText, 12,8)

Result:

Note: started at position 12 (M) with length 8. You can omit the third argument if you want to extract a substring starting in the middle of a string, until the end of the string.

Len

Len() function is used to return the length of the given string.

Try following code for better understanding:

Dim strText As String

strText = “Welcome to MS Excel Tutorial”

MsgBox Len(strText)

Result:

Instr

Instr function is used to find the occurrence of a substring within a main string. If the substring is found, it will return the point of occurrence.

Try following code for better understanding:

Dim strText As String

strText = “Welcome to MS Excel Tutorial”

MsgBox Instr(strText, “Excel”)

Result:

Note: string “Excel” found at position 15.