Lesson 8: The String Functions


Handling the string is relatively easy in Excel VBA as there are many built-in functions. Some of the common string functions are Left, Right, Instr, Mid, and Len.

8.1 The InStr function

InStr is a function that looks for and returns the position of a substring in a phrase

Example 8.1
Private Sub cmdInstr_Click()

Dim phrase As String
phrase = Cells(1, 1).Value
Cells(4, 1) = InStr(phrase, "ual")
	   
End Sub

* The function InStr(phrase,"ual") will search for the substring "ual" from the phrase "Visual Basic"  entered in cells(1,1) and then return its position, in this case, it is 4 from the left.

8.2  The Left function

Left is a function that extracts the characters from a phrase, starting from the left.

Left(phrase,4) means 4 characters are extracted from the phrase, starting from the leftmost position.

Example 8.2
Private Sub cmdLeft_Click()

Dim phrase As String
phrase = Cells(1, 1).Value
Cells(2, 1) = Left(phrase, 4)

End Sub

This code returns the substring "Visu" from the phrase "Visual Basic" entered in cells(1,1)

8.3  The Right function

Right is a function that extracts characters from a phrase, starting from the Right. Right(phrase,5) means 5 characters are extracted from the phrase, starting from the rightmost position.

Example 8.3
Private Sub cmdRight_Click()

Dim phrase As String
phrase = Cells(1, 1).Value
Cells(3, 1) = Right(phrase, 5)

End Sub

This code returns the substring "Basic" from the phrase "Visual Basic" entered in cells(1,1)

8.4  The Mid function

Mid is a function that extracts a substring  from a phrase, starting from the position specified by the second parameter in the bracket.

Mid(phrase,8,3) means a substring of three characters are extracted from the phrase, starting from the 8th position from the left, including empty space.

Example 8.4
Private Sub cmdMid_Click()

Dim phrase As String
phrase = Cells(1, 1).Value
Cells(5, 1) = Mid(phrase, 8, 3)

End Sub

This code returns the substring "Bas" from the phrase "Visual Basic" entered in cells(1,1)

8.5  The Mid function

Len is a function that returns the length of  a phrase(including empty space in between)

Example 8.5
Private Sub cmdLen_Click()

Dim phrase As String
phrase = Cells(1, 1).Value
Cells(6, 1) = Len(phrase)

End Sub

The code returns 12 for the phrase "Visual Basic" entered in cells(1,1)


The output of all the examples are shown in Figure 8.1 below:

Figure 8.1

❮ Previous Lesson Next Lesson ❯


Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved   [Privacy Policy]

Contact: Facebook Page