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.
We use the & operator to concatenate (join) strings.
text1 = “Hi”
text2 = “Tim”
MsgBox text1 & ” ” & text2
Note: to insert a space, use ” “
To extract the leftmost characters from a string, use Left.
text = “example text”
MsgBox Left(text, 4)
To extract the rightmost characters from a string, use Right. We can also directly insert text in a function.
To extract a substring, starting in the middle of a string, use 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.
To get the length of a string, use Len.
Note: space (position 8) included!
To find the position of a substring in a string, use Instr.
Note: string “am” found at position 3.