Chapter 9 – Build-in-functions – String
Instr Function

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
StrReverse Function

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
LCase Function

Definition: Returns string with all characters in lowercase
Syntax:
LCase(String)
Example:
Code:
LCase("World Is BEAUTIFUL")
Result: world is beautiful
UCase Function

Definition: Returns string with all characters in uppercase
Syntax:
UCase(String)
Example:
Code:
UCase("World is beautiful")
Result: WORLD IS BEAUTIFUL
Left Function

Definition: Returns the sub-string from a string from the left
Syntax:
Left(String, Length)
Example:
Code:
Left("World is beautiful", 5)
Result: World
Right Function

Definition: Returns the sub-string from a string from the right
Syntax:
Right(String, Length)
Example:
Code:
Right("World is beautiful", 9)
Result: beautiful
Mid Function

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

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

Definition: Returns the count of characters (including space) in a string
Syntax:
Len(String)
Example:
Code:
Len("World is beautiful")
Result: 18
Replace Function

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