String Manipulation with Excel VBA String Functions – Complete Tutorial

String Manipulation with Excel VBA String Functions

This chapter covers essential Excel VBA string functions that help you work with, modify, and analyze text strings efficiently.
Insert a command button on your worksheet, then enter the following code. Press the button once to see the result.

💎 Join Strings

We use the & operator to join two or more text strings together.

Code:

Dim text1 As String, text2 As String
text1 = "Hi"
text2 = "Tim"

MsgBox text1 & " " & text2

Result:

VBA String Functions

Note: To insert a space, type ” ” (double quotation marks with a space inside).

💎 Left

It is useful when you need to extract the first few letters, digits, or symbols from a text string.

Code:

Dim text As String
text = "example text"

MsgBox Left(text, 4)

Result:

💎 Right

It is useful when you need to extract the last few letters, digits, or symbols from a text string. You can enter the text directly into the function.

Code:

MsgBox Right("example text", 2)

Result:

💎 Mid

This feature helps you select a specific part of a text string by defining where to start and how many characters to take.

Code:

MsgBox Mid("example text", 9, 2)

Result:

Note: It starts from position 9, where the letter is ‘t’, and takes 2 characters. Without the third argument, it shows the text starting from the middle to the end.

💎 Len

The Len function counts all characters in a string, including letters, numbers, spaces, and symbols, to return the total length.

Code:

MsgBox Len("example text")

Result:

Note: space (position 8) included!

💎 InStr

You can use this when checking if a smaller text is present within a larger text and where it begins.

Code:

MsgBox InStr("example text", "am")

Result:

Note: string “am” found at position 3. Visit our page about the InStr function to explore more examples and explanations.

1/6 Completed! Learn more about string manipulation ➝
Next Chapter: Date and Time