D.5.1- String Functions and Statements

Format
Currency

Formats an expression as a currency value with the current currency symbol. The currency symbol is defined in Regional Settings in the Control Panel

Syntax

FormatCurrency(expression [,numdigitsafterdecimal [,includeleadingdigit [,useparensfornegativenumbers [,groupdigits]]]])

expression is the expression that you want formatted.

numdigitsafterdecimal (Optional) is a numeric value that indicates how many places to the right of the decimal separator should be displayed. If you omit this argument, the default value (-1) will be assumed and the settings from Control Panel will be used.

includeleadingdigit (Optional) indicates if a leading zero is displayed for fractional values. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Number tab in
Control Panel)
vbtrue -1

vbfalse 0

useparensfornegativenumbers (Optional) indicates if negative numbers are enclosed in parentheses. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Regional Settings tab
in Control Panel)
vbTrue -1

vbFalse 0

groupdigits (Optional) indicates if numbers are grouped using the thousand separator specified in Control Panel. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Regional Settings tab
in Control Panel)
vbtrue -1

vbfalse 0

Note

The way the currency symbol is placed in relation to the currency value
is determined by the settings in the Regional Settings tab in Control Panel. (Is the currency symbol placed before the number, after the number, is there a space between the symbol and the number and so on.)

Example

MsgBox FormatCurrency(7500)
MsgBox FormatCurrency(7500, , vbtrue)
MsgBox FormatCurrency(7500, 2, vbtrue)

If the currency symbol is a pound sign (£), the thousand separator a comma (,), and the currency symbol placed in front of the number with no spaces between, then MsgBox will display £7,500.00 in all of the above statements.

See Also

FormatDateTime, FormatNumber and FormatPercent

FormatDateTime

Returns a string formatted as a date and/or time.

Syntax

FormatDateTime(date, [namedformat])

date is any valid date expression.

namedformat (Optional) is a numeric value that indicates the date/time format used. Use one of the following constants:

vbGeneralDate 0 Format date (if present) and time (if present)
using the short date and long time format
from the machine's locale settings.

vbLongDate 1 Format date using the long date format from
the machine's locale settings.

vbShortDate 2 Format date using the short date format from
the machine's locale settings.

vbLongTime 3 Format time using the long time format from
the machine's locale settings.

vbShortTime 4 Format time using the short time format from
the machine's locale settings.

Note

A runtime error occurs if date is not a valid date expression. Null will be returned if date contains Null.

Example

MsgBox FormatDateTime(Now, vbShortDate)

On July 29, 1999 the MsgBox will display 07/29/99, if the locale settings use mm/dd/yy as the short date order and the forward
slash (/) as the date separator.

See Also

FormatCurrency, FormatNumber, and FormatPercent

FormatNumber

Returns a string formatted as a number.

Syntax

FormatNumber (expression,

[, numdigitsafterdecimal

[, includeleadingdigit

[, useparensfornegativenumbers [, groupDigits]]]])

expression is the expression that you want formatted.

numdigitsafterdecimal (Optional) is a numeric value that indicates
how many places to the right of the decimal separator should be displayed. If you omit this argument, the default value (-1) will be assumed and the settings from Control Panel will be used.

includeleadingdigit (Optional) indicates if a leading zero is displayed for fractional values. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Number tab in Control
Panel)
vbtrue -1

vbfalse 0

useparensfornegativenumbers (Optional) indicates if negative numbers are enclosed in parentheses. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Regional Settings tab in
Control Panel)
vbtrue -1

vbfalse 0

groupdigits (Optional) indicates if numbers are grouped using the thousand separator specified in Control Panel. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Regional Settings tab in
Control Panel)
vbtrue -1

vbfalse 0

Note

The Number tab in Regional Settings in Control Panel supplies all the information used for formatting.

Example

MsgBox FormatNumber("50000", 2, vbtrue, vbfalse, vbtrue)
MsgBox FormatNumber("50000")

The MsgBox will display 50,000.00, if the locale settings use a comma (,)
as the thousand separator and a period (.) as the decimal separator.

See Also

FormatCurrency, FormatDateTime, and FormatPercent

FormatPercent

Returns a string formatted as a percentage, like 50%.

Syntax

FormatPercent(expression,

[, numdigitsafterdecimal

[, includeleadingdigit

[, useparensfornegativenumbers [,groupDigits]]]])

expression is any valid expression that you want formatted.

numdigitsafterdecimal (Optional) is a numeric value that indicates
how many places to the right of the decimal separator should be displayed. If you omit this argument, the default value (-1) will be assumed and the settings from Control Panel will be used.

includeleadingdigit (Optional) indicates if a leading zero is displayed for fractional values. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Number tab in Control
Panel)
vbtrue -1

vbfalse 0

useparensfornegativenumbers (Optional) indicates if negative numbers are enclosed in parentheses. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Regional Settings tab in
Control Panel)
vbtrue -1

vbfalse 0

groupdigits (Optional) indicates if numbers are grouped using the thousand separator specified in Control Panel. Use one of the following constants:

vbUseDefault 2 (Uses the settings from the Regional Settings tab in
Control Panel)
vbtrue -1

vbfalse 0

Note

The Number tab in Regional Settings in Control Panel supplies all the information used for formatting.

Example

MsgBox FormatPercent(4 / 45)
MsgBox FormatPercent(4 / 45, 2, vbtrue, vbtrue, vbtrue)

The MsgBox will display 8.89%, if the locale settings use a period (.) as the decimal separator.

See Also

FormatCurrency, FormatDateTime, and FormatNumber

InStr

Returns an integer indicating the position for the first occurrence of a sub string within a string.

Syntax

InStr([start,] string1, string2[, compare])

start (Optional) is any valid non-negative expression indicating the starting position for the search within string1. Non-integer values are rounded. This argument is required if the compare argument is specified.

string1 is the string you want to search within.

string2 is the sub string you want to search for.

compare (Optional) indicates the comparison method used when evaluating. Use one of the following constants:

Syntax

vbBinaryCompare 0 (Default) Performs a binary comparison, i.e. a case
sensitive comparison.
vbTextCompare 1 Performs a textual comparison, i.e. a non-case
sensitive comparison.

Note

A runtime error will occur, if start contains Null. If start is larger than
the length of string2 (> Len(string2)) 0 will be returned.

Possible return values for different stringx settings:

string1 zero-length 0

string1 Null Null

string2 zero-length start

string2 Null Null

string2 not found 0

string2 found Position

Example

Dim lngStartPos
Dim lngFoundPos
Dim strSearchWithin
Dim strSearchFor

' Set the start pos

lngStartPos = 1
' Initialize the strings

strSearchWithin = "This is a test string"

strSearchFor = "t"

' Find the first occurrence

lngFoundPos = InStr( lngStartPos, strSearchWithin, strSearchFor)

' Loop through the string

Do While lngFoundPos > 0

' Display the found position

MsgBox lngFoundPos

' Set the new start pos to
' the char after the found position

lngStartPos = lngFoundPos + 1

' Find the next occurrence

lngFoundPos = InStr( lngStartPos, strSearchWithin, strSearchFor)
Loop

The above code finds all occurrences of the letter t in string1, at position 11, 14 and 17. Please note that we use binary comparison here, which means that the uppercase T will not be "found". If you want to perform a case-insensitive search, you will need to specify the compare argument as vbTextCompare.

See Also

InStrB, InStrRev

InStrB

Returns an integer indicating the byte position for the first occurrence of a sub string within a string containing byte data.

Syntax

InStrB([start,] string1, string2[, compare])

start (Optional) is any valid non-negative expression indicating the starting position for the search within string1. Non-integer values are rounded. This argument is required, if the compare argument is specified.

string1 is the string containing byte data you want to search within.

string2 is the sub string you want to search for.

compare (Optional) indicates the comparison method used when evaluating.
Use one of the following constants:

vbBinaryCompare – 0 (Default) Performs a binary comparison, i.e. a case
sensitive comparison.
vbTextCompare – 1 Performs a textual comparison, i.e. a non-case sensitive
comparison.

Note

A runtime error will occur, if start contains Null. If start is larger than the length of string2 (> Len(string2)) 0 will be returned.

Possible return values for different stringx settings:

string1 zero-length 0

string1 Null Null

string2 zero-length start

string2 Null Null

string2 not found 0

string2 found Position

Example

Dim lngStartPos
Dim lngFoundPos
Dim strSearchWithin
Dim strSearchFor

' Set the start pos

lngStartPos = 1
' Initialize the strings

strSearchWithin = "This is a test string"

strSearchFor = ChrB(0)

' Find the first occurrence

lngFoundPos = InStrB( lngStartPos, strSearchWithin, strSearchFor)

' Loop through the string
Do While lngFoundPos > 0
' Display the found position
MsgBox lngFoundPos

' Set the new start pos to
' the char after the found position

lngStartPos = lngFoundPos + 1

' Find the next occurrence

lngFoundPos = InStrB( lngStartPos, strSearchWithin, strSearchFor)
Loop

The above code finds all occurrences of the byte value 0 in string1, at position 2, 4, 6, ...40 and 42. This is because only the first byte of the Unicode character is used for the character. If you use a double-byte character set like the Japanese, the second byte will also contain a non-zero value.

See Also

InStr, InStrRev

InStrRev

Returns an integer indicating the position of the first occurrence of a sub string within a string starting from the end of the string. This is the reverse functionality
of InStr.

Syntax

InStrRev(string1, string2[, start[, compare]])

string1 is the string you want to search within.

string2 is the sub string you want to search for.

start (Optional) is any valid non-negative expression indicating the starting position for the search within string1; –1 is the default and it will be used if this argument is omitted.

compare (Optional) indicates the comparison method used when evaluating. Use one of the following constants:

vbBinaryCompare – 0 (Default) Performs a binary comparison, i.e. a case sensitive comparison.
vbTextCompare – 1 Performs a textual comparison, i.e. a non-case sensitive comparison.

Note

A runtime error will occur, if start contains Null. If start is larger than the length if string2 (> Len(string2)) 0 will be returned.

Possible return values for different stringx settings:

string1 zero-length 0

string1 Null Null

string2 zero-length start

string2 Null Null

string2 not found 0

string2 found Position

InStrRev and InStr do not have same syntax!

Example

Dim lngStartPos
Dim lngFoundPos
Dim strSearchWithin
Dim strSearchFor

' Set the start pos
lngStartPos = -1
' Initialize the strings
strSearchWithin = "This is a test string"
strSearchFor = "t"

' Find the first occurrence
lngFoundPos = InStrRev( strSearchWithin, strSearchFor, lngStartPos)

' Loop through the string
Do While lngFoundPos > 0
' Display the found
' position
MsgBox lngFoundPos

' Set the new start pos to
' the char before the found position
lngStartPos = lngFoundPos - 1

' Find the next occurrence
lngFoundPos = InStrRev( strSearchWithin, strSearchFor,-
lngStartPos)
Loop

The above code finds all occurrences of the letter t in string1, at position 17, 14 and 11. Please note that we use binary comparison here, which means that the uppercase T will not be "found". If you want to perform a case-insensitive search, you will need to specify the compare argument as vbTextCompare.

See Also

InStr, InStrB

Join

Joins a number of substrings in an array to form the returned string.

Syntax

Join(list[, delimiter])

list is a one dimensional array that contains all the substrings that you want to join.

delimiter (Optional) is the character(s) used to separate the substrings. A space character " " is used as the delimiter if this argument is omitted.

Note

All the substrings are concatenated with no delimiter if a zero-length string is used as delimiter. If any element in the array is empty, a zero-length string will be used as the value.

Example

Dim strLights

Dim arrstrColors(3)

' Fill the array

arrstrColors(0) = "Red"
arrstrColors(1) = "Yellow"
arrstrColors(2) = "Green"

' Join the array into a string

strLights = Join( arrstrColors, ",")

strLights contains "Red,Yellow,Green".

See Also

Split

LCase

Converts all alpha characters in a string to lowercase.

Syntax

LCase(string)

string is the string you want converted to lowercase.

Note

Null is returned if string contains Null. Only uppercase letters are converted.

Example

MsgBox LCase("ThisIsLowerCase")

MsgBox displays thisislowercase

See Also

UCase

Left

Returns length number of leftmost characters from string.

Syntax

Left(string, length)

string is the string you want to extract a number of characters from.

length is the number of characters you want to extract starting from the left. The entire string will be returned if length is equal to or greater than the total number
of characters in string.

Note

Null is returned if string contains Null.

Example

Dim strExtract

strExtract = "LeftRight"
MsgBox Left(strExtract, 4)

MsgBox displays Left.

See Also

Len, LenB, Mid, MidB and Right

Len

Returns the number of characters in a string.

Syntax

Len(string)

string is any valid string expression you want the length of.

Note

Null is returned if string contains Null.

Example

Dim strLength

strLength = "1 2 3 4 5 6 7 8 9"
MsgBox Len(strLength)

MsgBox displays 17.

See Also

Left, LenB, Mid, MidB and Right

LenB

Returns the number of bytes used to represent a string.

Syntax

LenB(string)

string is any valid string expression you want the number of bytes for.

Note

Null is returned if string contains Null.

Example

Dim strLength

strLength = "123456789"
MsgBox LenB(strLength)

MsgBox displays 18.

See Also

Left, Len, Mid, MidB and Right

LTrim

Trims a string of leading spaces; " " or Chr(32).

Syntax

LTrim(string)

string is any valid string expression you want to trim leading
(leftmost) spaces from.

Note

Null is returned if string contains Null.

Example

Dim strSpaces

strSpaces = " Hello again *"
MsgBox LTrim(strSpaces)

MsgBox displays Hello again *

See Also

Left, Mid, Right, RTrim and Trim

Mid

Returns a specified number of characters from any position in a
string.

Syntax

Mid(string, start[, length])

string is any valid string expression you want to extract characters from.

start is the starting position for extracting the characters. A zero-
length string is returned if it is greater than the number of characters
in string.

length (Optional) is the number of characters you want to extract. All characters from start to the end of the string are returned if this argument is omitted or if length is greater than the number of characters counting from start.

Note

Null is returned if string contains Null.

Example

Dim strExtract

strExtract = "Find ME in here"
MsgBox Mid(strExtract, 6, 2)

MsgBox displays ME

See Also

Left, Len, LenB, LTrim, MidB, Right, RTrim and Trim

MidB

Returns a specified number of bytes from any position in a string containing byte data.

Syntax

MidB(string, start[, length])

string is a string expression containing byte data you want to extract characters from.

start is the starting position for extracting the bytes. A zero-length string
is returned if it is greater than the number of bytes in string.

length (Optional) is the number of bytes you want to extract. All bytes from start to the end of the string are returned if this argument is omitted or if length is greater than the number of bytes counting from start.

Note

Null is returned if string contains Null.

Example

Dim strExtract

strExtract = "Find ME in here"
MsgBox MidB(strExtract, 11, 4)

MsgBox displays ME , because VBScript uses 2 bytes to represent a character. The first byte contains the ANSI character code when dealing with 'normal' ANSI characters like M, and the next byte is 0. So byte 11 in the string is the first byte for the letter M and then we extract 4 bytes/2 characters.

See Also

Left, Len, LTrim, Mid, Right, RTrim and Trim

Replace

Replaces a substring within a string with another substring a specified number of times.

Syntax

Replace(expression, find, replacewith[, start[, count[, compare]]]))

expression is a string expression that contains the substring you want to replace.

find is the substring you want to replace.

replacewith is the substring you want to replace with.

start (Optional) is the starting position within expression for replacing the substring. 1 (default), the first position, will be used if this argument is omitted. You must also specify the count argument if you want to use
start.

count (Optional) is the number of times you want to replace find. -1 (default) will be used if this argument is omitted, which means all find in the expression. You must also specify the start argument if you want to use count.

compare (Optional) indicates the comparison method used when evaluating. Use one of the following constants:

vbBinaryCompare – 0 (Default) Performs a binary comparison, i.e. a case sensitive comparison.
vbTextCompare – 1 Performs a textual comparison, i.e. a non-case sensitive comparison.

Note

If start and count are specified, the return value will be the original expression, with find replaced count times with replacewith, from start to the end of the expression, and not the complete string. A zero-length string is returned if start is greater than the length of expression (start > Len(expression)). All occurrences of find will be removed if replacewith
is a zero-length string ("")

Possible return values for different argument settings:

expression zero-length zero-length

expression Null Error

find zero-length expression

count 0 expression

Example

Dim strReplace

strReplace = Replace( "****I use binary", "I", "You", 5, _
1, vbBinaryCompare) ' You use binary

strReplace = Replace( "****I use text", "i", "You", , , _
vbTextCompare) ' ****You use text

See Also

Left, Len, LTrim, Mid, Right, RTrim and Trim

Right

Returns length number of rightmost characters from string

Syntax

Right(string, length)

string is the string you want to extract a number of characters from.

length is the number of characters you want to extract starting from the
right. The entire string will be returned if length is equal to or greater than the total number of characters in string.

Note