Excel VBA Lesson 8: String Manipulation Functions

<<Lesson 7>> [Contents] <<Lesson 9>>

Excel VBA can handle strings just as well as the stand-alone Visual Basic program. All the string handling functions in Visual Basic such as Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str ,Chr and Asc can be used in Excel VBA.

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



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

he Right 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 8.3

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

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 Len 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)

Visual Basic Editor in MS Excel can handle strings just as good as a stand-alone VB program. All the string handling functions in Visual Basic such as Left, Right, Instr, Mid and Len can be used in Excel Visual Basic Editor.

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

Excel VBA

8.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



8.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.

8.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 format of the Chr function is

Chr(charcode)

and the format 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

<<Lesson 7>> [Contents] <<Lesson 9>>