String Manipulation

 

Join Strings | Left | Right | Mid | Len | Instr

In this chapter, you’ll find the most important functions to manipulate strings in Excel VBA.

Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.

Join Strings

We use the & operator to concatenate (join) strings.

Code:

Dim text1 As String, text2 As String

text1 = “Hi”

text2 = “Tim”

MsgBox text1 & ” ” & text2

Result:

Join Strings

Note: to insert a space, use ” ”

Left

To extract the leftmost characters from a string, use Left.

Code:

Dim text As String

text = “example text”

MsgBox Left(text, 4)

Result:

Left

Right

To extract the rightmost characters from a string, use Right. We can also directly insert text in a function.

Code:

MsgBox Right(“example text”, 2)

Result:

Right

Mid

To extract a substring, starting in the middle of a string, use Mid.

Code:

MsgBox Mid(“example text”, 9, 2)

Result:

Mid

Note: started at position 9 (t) with length 2. 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

To get the length of a string, use Len.

Code:

MsgBox Len(“example text”)

Result:

Len

Note: space (position 8) included!

Instr

To find the position of a substring in a string, use Instr.

Code:

MsgBox Instr(“example text”, “am”)

Result:

Instr

Note: string “am” found at position 3. Visit our page about the Instr function for more information and examples.

Previous articleHow to use XLOOKUP in Excel
Next articleArray Function in Excel VBA