Table of Contents
String Manipulation
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:
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:
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:
Mid
To extract a substring starting in the middle of a string use Mid.
Code:
MsgBox Mid(“example text” 9 2)
Result:
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:
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:
Note: string “am” found at position 3. Visit our page about the Instr function for more information and examples.
