String Functions

Back to top

Format

Description

Returns a string formatted according to instructions contained in a format String expression.

Syntax

  • Format( expression, style )

Arguments

  • expression
    • Any valid expression.
  • style
    • Optional. A valid named or user-defined format String expression.

A number of good references can be found on the web for the format function.
Comprehensive guide to the format function can be found on the Microsoft website: https://support.office.com/en-us/article/Format-Function-6F29D87B-8761-408D-81D3-63B9CD842530

Example

Dim MyTime, MyDate, MyStr
MyTime = #17:04:23#
MyDate = #January 27, 1993#

' Returns current system time in the system-defined long time format.

MyStr = Format(Time, "Long Time")
' Returns current system date in the system-defined long date format.

MyStr = Format(Date, "Long Date")
MyStr = Format(MyTime, "h:m:s") ' Returns "17:4:23".
MyStr = Format(MyTime, "hh:mm:ss AMPM") ' Returns "05:04:23 PM".
MyStr = Format(MyDate, "dddd, mmm d yyyy") ' Returns "Wednesday,' Jan 27 1993".

' If format is not supplied, a string is returned.

MyStr = Format(23) ' Returns "23".

' User-defined formats.

MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40".
MyStr = Format(334.9, "###0.00") ' Returns "334.90".
MyStr = Format(5, "0.00%") ' Returns "500.00%".
MyStr = Format("HELLO", "<") ' Returns "hello".
MyStr = Format("This is it", ">") ' Returns "THIS IS IT".

Back to top

FormatMessage

Description

Provides an easy means to create a string which is parameterised with values.

This function is useful when constructing messages to be used in the WriteToLog or Lookup Function, but can be used anywhere where a string needs to be built dynamically.

Syntax

  • FormatMessage ( message, values )

Arguments

  • Message
    • The message to format. A parameter is specified by a ‘%’ symbol followed by the incrementing value of the parameter. For example the first parameter is %1, the second parameter is %2 and so on.
  • values
    • The value(s) to replace the parameters within the message.
    • One or more values to parameterise the message. The values are either a single value or an array of values.

Example

Format a string with a single parameter.

FormatMessage(“Customer %1 is not valid.”, %CustomerId)

Format a string with multiple parameters.

FormatMessage(“The %1 customer with email %2 is not valid.”, Array(%CustomerType, %CustomerId))

Back to top

InStr

Description

Returns the position of the first occurrence of a string in another string.

Syntax

  • InStr( [start], string_being_searched, string2, [compare] )

Arguments

  • start
    • Optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.
  • string_being_searched
    • The string that will be searched.
  • string2
    • The string to search for.
  • compare
    • Optional. This is the type of comparison to perform. The valid choices are:

Value

Explanation

0

Binary comparison.

1

Textual comparison.
   
Back to top

InStrRev

Description

Returns the position of the first occurrence of a string in another string, starting from the end of the string.

Syntax

  • InstrRev ( string_being_searched, string2 [, start [ , compare] ] )

Arguments

  • string_being_searched
    • The string that will be searched.
  • string2
    • The string to search for.
  • start
    • Optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position -1 which is the last character position.
  • compare
    • Optional. This is the type of comparison to perform. The valid choices are:

Value

Explanation

0

Binary comparison.

1

Textual comparison.
   
Back to top

LCase

Description

Converts a string to lower-case.

Syntax

  • LCase( text )

Arguments

  • text
    • The string that you wish to convert to lower-case.
Back to top

Left

Description

Returns a substring from a string, starting from the left-most character.

Syntax

  • Left( text, number_of_characters )

Arguments

  • text
    • The string that you wish to extract from.
  • number_of_characters
    • Indicates the number of characters that you wish to extract starting from the left-most character.
Back to top

Len

Description

Returns the length of the specified string.

Syntax

  • Len( text )

Arguments

  • text
    • The string to return the length for.
Back to top

LTrim

Description

Removes leading spaces from a string.

Syntax

  • LTrim( text )

Arguments

  • text
    • The string that you wish to remove leading spaces from.
Back to top

Trim

Description

Returns a text value with the leading and trailing spaces removed.

Syntax

  • Trim( text )

Arguments

  • text
    • The text value to remove the leading and trailing spaces from.
Back to top

Mid

Description

Extracts a substring from a string (starting at any position).

Syntax

  • Mid( text, start_position, number_of_characters )

Arguments

  • text
    • The string that you wish to extract from.
  • start_position
    • Indicates the position in the string that you will begin extracting from. The first position in the string is 1.
  • number_of_characters
    • Indicates the number of characters that you wish to extract.
Back to top

Replace

Description

Replaces a sequence of characters in a string with another set of characters.

Syntax

  • Replace( string, find, replacewith, [,start[,count[,compare]]]) )

Arguments

  • string
    • The string to be searched.
  • find
    • Part of the string that will be replaced.
  • replacewith
    • The replacement substring.
  • start
    • Optional. Is the position in string to begin replacing characters.
  • count
    • Optional. Specifies the number of substitutions to perform.
      Default value is -1, which means make all possible substitutions.
  • compare
    • Optional. Numeric value indicating the kind of comparison to use when evaluating strings. If omitted, a binary comparison is performed. The valid choices are:

Value

Explanation

0

Binary comparison.

1

Textual comparison.
   
Back to top

Right

Description

Extracts a substring from a string starting from the right-most character.

Syntax

  • Right( text, number_of_characters )

Arguments

  • text
    • The string that you wish to extract from.
  • number_of_characters
    • Indicates the number of characters that you wish to extract starting from the right-most character.
Back to top

RTrim

Description

Removes trailing spaces from a string.

Syntax

  • RTrim( text )

Arguments

  • text
    • The string that you wish to remove trailing spaces from.
Back to top

Space

Description

Returns a string with a specified number of spaces.

Syntax

  • Space( number )

Arguments

  • number
    • The number of spaces to be returned.
Back to top

StrComp

Description

Returns a value indicating the result of a string comparison.

Syntax

  • StrComp(string1, string2[, compare])

Arguments

  • string1
    • Any valid string expression.
  • string2
    • Any valid string expression.
  • compare
    • Optional. Numeric value indicating the kind of comparison to use when evaluating strings. If omitted, a binary comparison is performed. The valid choices are:

Value

Explanation

0

Binary comparison.

1

Textual comparison.
   
Back to top

String

Description

Returns a repeating character string of the length specified.

Syntax

  • String(number, character)

Arguments

  • number
    • The length of the returned string. If number contains Null, Null is returned.
  • character
    • The character code specifying the character or string expression whose first character is used to build the return string. If character contains Null, Null is returned. If you specify a number for character greater than 255, String converts the number to a valid character code using the modulus of 256.
Back to top

StringLike

Description

Compares a string against a pattern. If the value in string satisfies the pattern contained in pattern, result is True. If the string does not satisfy the pattern, result is False. If both string and pattern are empty strings, the result is True.

Syntax

  • StringLike( string, pattern )

Arguments

  • string
    • A string expression.
  • pattern
    • The pattern to compare the string to.
    • Built-in pattern matching provides a versatile tool for string comparisons.
    • The pattern-matching features allow you to match each character in string against a specific character, a wildcard character, a character list, or a character range. The following table shows the characters allowed in pattern and what they match.

Characters in pattern

Matches in string
? Any single character
* Zero or more characters
# Any single digit (0–9)
[charlist] Any single character in charlist
[!charlist] Any single character not in charlist

 

 
Back to top

StrReverse

Description

Returns a string in which the character order of a specified string is reversed.

Syntax

  • StrReverse(string)

Arguments

  • string
    • The string whose characters are to be reversed. If string is a zero-length string (""), a zero-length string is returned. If string is Null, an error occurs.
Back to top

TitleCase

Description

Returns a string where the first letter to every word  is uppercase and all other characters are left as lowercase.

Syntax

  • TitleCase( string )

Arguments

  • string
    • The string to be formatted.
Back to top

UCase

Description

Converts a string to all upper-case.

Syntax

  • UCase( text )

Arguments

  • text
    • The string that you wish to convert to upper-case.