String Manipulation

 

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

There are many functions in Excel VBA we can use to manipulate strings. Below you can find a review of the most important functions.

Place a command button on your worksheet and add the code lines described in this chapter. 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 Two Strings

Left

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

Code:

Dim text As String
text = "example text"

MsgBox Left(text, 4)


Result:

String Manipulation Left Function

Right

To extracts the rightmost characters from a string, use Right. We can directly insert text in a function as well.

Code:

MsgBox Right("example text", 2)


Result:

Right Function

Len

To get the length of a string, use Len.

Code:

MsgBox Len("example text")


Result:

Len Function

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 Function

Note: string "am" found at position 3.

Mid

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

Code:

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


Result:

Mid function

Note: started at position 9 (t) with length 4.

Did you find this information helpful? Show your appreciation, vote for us.

Go to Top: String Manipulation    |    Go to Next Topic: Calculate

More about string manipulation, Login to the right >>
100 easy to follow Excel VBA examples. Limited time-offer: $39.00 but only $29.00. Ends on 29th February.