Lesson 9 String Manipulation Functions

All the string handling functions in VB can be used in Excel VBA. These functions include Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str, Chr, and Asc.

9.1 The InStr function

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

Example 9.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 find 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.

9.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 9.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)

9.3 The Right function

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

Right(phrase,5) means 5 characters are extracted from the phrase, starting from the rightmost position.

9.3 The Right function

Right is a function that extracts the 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 9.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)

9.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 9.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)

9.5 The Len function

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

Example 9.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)

9.6 The Ucase and the Lcase functions

The Ucase function converts all the characters of a string to capital letters. On the other hand, the Lcase function converts all the characters of a string to small letters. For example,

Ucase("excel vba") =EXCEL VBA

Lcase("Excel VBA") =excel vba

9.7 The Str and Val functions

The Str is the function that converts a number to a string while the Val function converts a string to a number. The two functions are important when we need to perform mathematical operations.

9.8 The Chr and the Asc functions

The Chr function returns the string that corresponds to an ASCII code while the Asc function converts an ASCII character or symbol to the corresponding ASCII code. ASCII stands for “American Standard Code for Information Interchange”. Altogether there are 255 ASCII codes and as many ASCII characters. Some of the characters may not be displayed as they may represent some actions such as the pressing of a key or produce a beep sound. The syntax of the Chr function is

Chr(charcode)

and the syntax of the Asc function is

Asc(Character)

The following are some examples:

Chr(65)=A, Chr(122)=z, Chr(37)=% , Asc("B")=66, Asc("&")=38