VBA – String Functions

Strings are a sequence of characters, which can consist of either alphabets, numbers, special characters, or all of them. A variable is said to be a string if it is enclosed within double quotes ” “.

Syntax

variablename = "string"

Examples

str1 = "string"   ' Only Alphabets
str2 = "132.45"   ' Only Numbers
str3 = "!@#$;*"  ' Only Special Characters
Str4 = "Asc23@#"  ' Has all the above

String Functions

There are predefined VBA String functions, which help the developers to work with the strings very effectively. Following are String methods that are supported in VBA. Please click on each one of the methods to know in detail.

S.No. Function Name & Description
1 InStrReturns the first occurrence of the specified substring. Search happens from the left to the right.
2 InstrRevReturns the first occurrence of the specified substring. Search happens from the right to the left.
3 LcaseReturns the lower case of the specified string.
4 UcaseReturns the upper case of the specified string.
5 LeftReturns a specific number of characters from the left side of the string.
6 RightReturns a specific number of characters from the right side of the string.
7 MidReturns a specific number of characters from a string based on the specified parameters.
8 LtrimReturns a string after removing the spaces on the left side of the specified string.
9 RtrimReturns a string after removing the spaces on the right side of the specified string.
10 TrimReturns a string value after removing both the leading and the trailing blank spaces.
11 LenReturns the length of the given string.
12 ReplaceReturns a string after replacing a string with another string.
13 SpaceFills a string with the specified number of spaces.
14 StrCompReturns an integer value after comparing the two specified strings.
15 StringReturns a string with a specified character for specified number of times.
16 StrReverseReturns a string after reversing the sequence of the characters of the given string.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s