VBA String Functions - Part 6

LTRIM Function (VBA)

In Microsoft Excel, the LTRIM function removes leading spaces from a string.

Syntax

LTRIM( text )
text is the string that you wish to remove leading spaces from.

VBA Function Example

The LTRIM function can only be used in VBA code in Microsoft Excel. Here are some examples of what the LTRIM function would return:

LTRIM("  Google com") would return "Google com"
LTRIM("  Alphabet") would return "Alphabet"
LTRIM("  Alphabet  ") would return "Alphabet  "

Dim sResult As String
sResult = LTrim("   Alphabet   ")

The variable sResult would now contain the value of "Alphabet  ".

VBA String Functions - Part 7

INSTR Function (VBA)

In Microsoft Excel, the INSTR function returns the position of the first occurrence of a substring in a string.

Syntax

The syntax for the INSTR function is:  

InStr( [start], string, substring, [compare] )
start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.

string is the string to search  within.

substring is the substring that you want to find.

VBA Function Example

The INSTR function can only be used in VBA code in Microsoft Excel. Here are some examples of what the INSTR function would return:

InStr(1, "President of India", "of") would return 11
InStr("President of India", "India") would return 14
InStr(5, "President of India", "e") would return 7

Dim iPosition As Integer 
iPosition = InStr(5, "President of India", "e")

In this example, the variable called iPosition would now contain the value 7.

VBA String Functions - Part 5

LEFT Function (VBA)

In Microsoft Excel, the LEFT function allows you to extract a substring from a string, starting from the left-most character.

Syntax

The syntax for the LEFT function is:

LEFT( text, [number_of_characters] )
text is the string that you wish to extract from.

number_of_characters is optional. It indicates the number of characters that you wish to extract starting from the left-most character. If this parameter is omitted, only 1 character is returned.

VBA Function Example

Dim sResult As String
sResult = Left("Alphabet",3)

The variable sResult would now contain the value of "Alp".

VBA String Functions - Part 4

STRCONV Function (VBA)

In Microsoft Excel, the STRCONV function returns a string converted as specified.

Syntax

The syntax for the STRCONV function is:

StrConv ( text, conversion, [LCID] )
text is the string that you wish to convert.

LCID is optional. If this parameter is omitted, the STRCONV function assumes the system LocaleID.

VBA Function Example

The STRCONV function can only be used in VBA code in Microsoft Excel. Here are some examples of what the STRCONV function would return:

StrConv("vbaexpress.blogspot.in", 1) would return "VBAEXPRESS.BLOGSPOT.IN"
StrConv("VBAEXPRESS.BLOGSPOT.IN", 2) would return "vbaexpress.blogspot.in"
StrConv("vbaexpress.blogspot.in", 3) would return "Vbaexpress.Blogspot.In"

Dim sResult As String
sResult = StrConv("VBAEXPRESS.BLOGSPOT.IN", vbProperCase)

In this example, the variable called sResult would now contain the value "Vbaexpress.Blogspot.In".

VBA String Functions - Part 3

LCASE Function (VBA)

In Microsoft Excel, the LCASE function converts a string to lower-case.

Syntax

The syntax for the LCASE function is:

LCase( text )
Text is the string that you wish to convert to lower-case.

VBA Function Example

The LCASE function can only be used in VBA code in Microsoft Excel. Here are some examples of what the LCASE function would return:

LCase("ALPHABET") would return "alphabet"
LCase("Google SearcH") would return "google search"
LCase("124ABC") would return "124abc"

Dim sResult As String
sResult = LCase("This is a TEST")

In this example, the variable called sResult would now contain the value "this is a test".

VBA String Functions - Part 2

FORMAT Function  (VBA)

In Microsoft Excel, the FORMAT function takes an expression and returns it as a formatted string.

Syntax

The syntax for the FORMAT function is:

Format ( expression, [ format ] )
expression is the value to format.

format is optional. It is the format to apply to the expression. You can either define your own format or use one of the named formats that Excel has predefined such as:

VBA Function Example

The FORMAT function can only be used in VBA code in Microsoft Excel. Here are some examples of what the FORMAT function would return:

Format("210.6", "#,##0.00") would return '210.60'
Format("210.6", "Standard") would return '210.60'
Format("0.981", "Percent") would return '98.10%'
Format("1267.5", "Currency") would return '$1,267.50'
Format("Sep 3, 2003", "Short Date") would return '9/3/2003'

Dim sValue As String
sValue = Format("0.981", "Percent")

In this example, the variable called sValue would now contain the value of '98.10%'.