Chapter 9 – Build-in-functions – String

In this chapter, we will learn different types of string build-in-functions used in VBA for string manipulation. We will also see practical example of each string function.

• Instr
• StrReverse
• LCase
• UCase
• Left
• Right
• Mid
• Trim
• Len
• Replace

Definition: Returns the number of a particular character within a string

Syntax:

InStr([Start], [String1], [String2], [Compare])

Example 1:

Code:

InStr(1, "World is beautiful", "Is")

Result: 0

Explanation: InStr is by default case sensitive that’s the reason it could not find “Is” and returned 0

Example 2:

Code:

InStr(1, "World is beautiful", "Is", vbTextCompare)

Result: 7

Explanation: By passing compare parameter as ‘vbTextCompare’, InStr became non case sensitive and could find “Is”

Example 3:

Code:

InStr(10, "World is beautiful", "is")

Result: 0

Explanation: Start parameter is passed as 10; hence the function will start looking for the characters from 10th position. As there is no matching characters in the first string after 10th character, it returned 0

Definition: Returns the string in reverse order. It is normally used to find character(s) from the right of the string. For example finding file extension from the file path.

Syntax:

StrReverse(String)

Example 1:

Code:

StrReverse("World is beautiful")

Result: lufituaeb si dlroW

Example 2:

Code:

Right("C:\Users\Kailash\Desktop\test.txt", InStr(1, StrReverse("C:\Users\Kailash\Desktop\test.txt"), "."))

Result: .txt

Explanation: The Code first finds “.” position from the reverse string of given file path and uses Right function to get those number of characters from right which in turns gives extension of the file

Example 3:

Code:

Right("What is my Last Name", InStr(1, StrReverse("What is my Last Name"), " "))

Result: Name

Explanation: The code finds space (“ ”) position from the reverse string of given full name and uses Right function to get those number of characters from right which in turns gives last name

Definition: Returns string with all characters in lowercase

Syntax:

LCase(String)

Example:

Code:

LCase("World Is BEAUTIFUL")

Result: world is beautiful

Definition: Returns string with all characters in uppercase

Syntax:

UCase(String)

Example:

Code:

UCase("World is beautiful")

Result: WORLD IS BEAUTIFUL

Definition: Returns the sub-string from a string from the left

Syntax:

Left(String, Length)

Example:

Code:

Left("World is beautiful", 5)

Result: World

Definition: Returns the sub-string from a string from the right

Syntax:

Right(String, Length)

Example:

Code:

Right("World is beautiful", 9)

Result: beautiful

Definition: Returns the specified number of characters from a string. Second parameter (Start) supplied in the function is treated as the starting position from which sub-string needs to be returns. The last parameter (Length) is an optional parameter to specify the number of characters to be returned from the starting position. If the last parameter (Length) is not supplied then this function returns entire right string from the starting position.

Syntax:

Mid(String, Start, [Length])

Example 1:

Code:

Mid("World is beautiful", 7, 2)

Result: is

Example 2:

Code:

Mid("World is beautiful", 7)

Result: is beautiful

Example 3:

Code:

Mid("World is beautiful", 30)

Result: blank

Explanation: The given string “World is beautiful” has only 18 characters and we have supplied second parameter (Start) as 30; hence this function returned blank as there is no character at 30th position.

Definition: Returns the string after removing leading and trailing spaces.

Syntax:

Trim(String)

Example 1:

Code:

Trim("  World is beautiful ")

Result: World is beautiful

Example 2:

Code:

Trim("  World   is beautiful")

Result: World is beautiful

Explanation: Trim function do not removes extra spaces available in-between the string

Definition: Returns the count of characters (including space) in a string

Syntax:

Len(String)

Example:

Code:

Len("World is beautiful")

Result: 18

Definition: The function replaces specified characters in a string with another set of characters

Syntax:

Replace(String, Find, Replace, [Start], [Count], [Compare])

Example 1:

Code:

Replace("World is beautiful", "is", "is very")

Result: World is very beautiful

Example 2:

Code:

Replace("india is in asia", "in", "xx")

Result: xxdia is xx asia

Example 3:

Code:

Replace("india is in asia", "in", "xx", 1, 1)

Result: xxdia is in asia

Explanation: In the function, fourth parameter (Count) has been supplied as 1; hence the function replaced only one occurrence of the characters

Example 4:

Code:

Replace("india is in asia", "in", "xx", 5, 1)

Result: a is xx asia

Explanation: In the function, third parameter (Start) has been supplied as 5; hence the function starts finding the characters from 5th position and returns string after 5th character

Example 5:

Code:

Replace("india is in asia", "xx", "zz")

Result: india is in asia

Explanation: Function did not find matching characters; hence returned same string