# String Functions

These functions manipulate strings.

Note:

- Index parameters are zero-based. In other words, the first character of a string has an index of 0, the second character has an index of 1, and so on.
- Text strings that are one character wider than the cell width are sometimes clipped. Increasing the column width or adding a space character to the string will display the entire string.

- String functions operate on strings of Unicode characters.

Function | Description
Code (Text, [Index]) | Returns the integer value corresponding to the Unicode code point for the character at the specified index. If no index is specified, returns the code of the first character. Returns zero if the string is empty or if the index is beyond the end of the string. Unicode code points are the same as ASCII for the first 127 characters, but the full range of Unicode is supported by this function. Code Example If cell A2 contains the alphanumeric string, "ABC123", then the function Code(A2) would return 65.000, the ASCII code for "A".
Concatenate (Arg1, [Arg2, Arg3, ...]) | Returns the concatenation of multiple alphanumeric text strings. Numeric arguments are automatically converted to text. Concatenate Example If cell A2 contains the alphanumeric string "A1B1", cell A3 contains the alphanumeric string "C1D2", and cell A4 contains the floating-point number 4.556, then the function Concatenate(A2,A3,A4) would return "A1B1C1D24.556"
Exact (Text1, Text2) | Returns 1 if Text1 and Text2 are identical, else 0. Note: Exact is case-sensitive. Exact Examples If cell A2 contains the string, "ABC123", and cell A3 contains the string, "ABC1234", then Exact(A2,A3) would return 0.000. If cell A2 contains the string, "VISION", and cell A3 contains the string, "VISION", then Exact(A2,A3) would return 1.000.
Find (FindText, SrcText, [StartChar]) | Returns the indexed position of the FindText string contained within the string SrcText. Note: Find is case-sensitive. Find Examples FindText and SrcText can be cell references or a string directly added to the functions arguments. Let cell A2 contain the string "123" and cell A3 contains the string "ABC123." Find(A2, A3) or Find("123", A3) would return 3.00 the index position of "123" within "ABC 123." StartChar is an optional (integer) argument representing a index position within the SrcText where to find will begin the search. Find(A2,A3,2) would start at index position 2 in SrcText and return the Index value 3.000. If FindText is not found, -1.00 is returned. Find("XYZ",A3) would return -1.000
FormatString (Format-string, [Text or Value, ...]) | Returns a text string constructed using a "C" library standard Format-string. FormatString supports most Format-string parameters. Use the Format String Dialog to automatically configure this function. Note: Manually editing the FormatString function is not recommended.
Left (Text, NumChars) | Returns the leftmost number of characters (NumChars) from a Text string. Left Example If cell A2 contains the alphanumeric string, "ABC123", then the function Left(A2,2) would return the string "AB".
Len (Text) | Returns the number of characters in a Text string. Len Example If cell A2 contains the alphanumeric string, "ABC123", then the function Len(A2) would return the integer value 6.000.
Lower (Text) | Returns Text converted to lowercase. Lower Example If cell A2 contains the alphanumeric string, "ABC123", then the function Lower(A2) would return the string "abc123".
Mid (Text, StartChar, NumChars) | Returns the Text starting from the indexed StartChar position in the alphanumeric string, for as many characters as specified by NumChars. Mid Example If cell A2 contains the alphanumeric string, "ABC123", then the function Mid(A2,2,2) would return the string "C1".
Replace (SrcText, StartChar, NumChars, NewText) | Returns SrcText, modified by NewText. Replace removes a number of characters NumChars starting at the indexed position StartChar and inserts the replacement text NewText. Replace Example If cell A2 contains the alphanumeric string, "ABC123", and cell A3 contains the string "123", then Replace(A2,0,3,A3) would return the string "123123".
Right (Text, NumChars) | Returns the rightmost number of characters NumChars from a string Text. Right Example If cell A2 contains the string, "ABC123", then Right(A2,2) would return the string "23".
Stringf (Format-string, [Text or Value, ...]) | Returns a text string constructed using a "C" library standard Format-string. Tip: The FormatString function supports most Format-string parameters; use the Format String Dialog to automatically configure the parameters. Note: The format-string must be enclosed in double quotes (i.e. "This is a string" .) The format-string parameters are case-sensitive. When using the Stringf function, the backslash "\" is used to indicate a format control character follows. If a backslash is not followed by a control character (such as "n", "r", or "t") then the character following the backslash is inserted. To display a single backslash, two backslashes must be entered. Example: Stringf("C:\\InSight2\\") = "C:\InSight2\" Non-printable/control code characters will be displayed as either a symbol or "garbage" text. Available Format-string parameters are:
%c | Returns the Unicode character corresponding to an integer Value. Stringf %c Example If cell A2 contains the integer value 65.000, then Stringf("%c",A2) would return the character "A", which is the character corresponding to ASCII code 65. The function Stringf("Value = %c",A2) would return the string "Value = A".
%d | Returns a string containing an integer Value. Stringf %d Examples If cell A2 contains the floating point value 123.456, then Stringf("%d",A2) would return the numeric string "123". The function Stringf("Value = %d",A2) would return the alphanumeric string "Value = 123".
%e | Returns a string containing a Scientific Notation Value. The number of decimal places may be specified by inserting a decimal point (.) and a number between the % and e parameters. Stringf %e Examples If cell A2 contains the floating point value 123.456, then Stringf("%e",A2) would return the numeric string "1.234560e+02". The function Stringf("%.1e",A2) would return the numeric string "1.2e+02", because only one number after the decimal point is specified.
%f | Returns a string containing a floating point Value. The number of decimal places may be specified by inserting a decimal point (.) and a number between the % and f parameters. Stringf %f Examples If cell A2 contains the floating point value 123.456, then Stringf("%f",A2) would return the numeric string "123.456001". The function Stringf("%.1f",A2) would return the numeric string "123.5", because only one number after the decimal point is specified.
%o | Returns a string containing the octal value for an integer. Stringf %o Examples If cell A2 contains the integer value 123.000, then Stringf("%o",A2) would return the numeric string "173", the octal value for the integer 123.
%s | Returns a string with alphanumeric characters appended. Stringf %s Examples If cell A2 contains the string "ABC", then Stringf("%s123",A2) would return the alphanumeric string "ABC123". If cell A2 contains the numeric string "123", then Stringf("%s.450",A2) would return the numeric string "123.450".
%u | Returns a string containing an unsigned integer Value. Stringf %u Examples If cell A2 contains the floating point value 123.456, then Stringf("%u",A2) would return the numeric string "123". The function Stringf("Value = %u",A2) would return the alphanumeric string "Value = 123".
%x or %X | Returns a string containing the hexadecimal value for an integer value. Stringf %x Examples If cell A2 contains the integer value 123.000, then Stringf("%x",A2) would return the string "7b", the hex value for the integer 123.
%% | Returns a string containing a single percent character (%).
The following Format-string parameters are used primarily for text formatting (for example, formatting the text of an email message in the function WriteSMTP):
\r | Inserts a carriage return into the text. This parameter is usually used in conjunction with \n (line feed).
\n | Inserts a new line into the text. This parameter is usually used in conjunction with \r (carriage return).
\\ | Inserts a backslash “\” into the text.
\t | Inserts a tab into the text.
\v | Inserts a vertical tab into the text.
\f | Inserts ASCII Formfeed into the text.
\x## | Inserts the ASCII or Unicode character specified by a hexadecimal number ## into the text. Stringf \x## Example If cell A2 contains Stringf("\x02123\x1bABC\x03456"), then the cell will display: ("<STX>123<ESC>ABC<ETX>456").
\u#### | Inserts the ASCII or Unicode character specified by a 16-bit hexadecimal number #### into the text.
Strtol (IntegerText) | Converts numeric characters (decimal, hexadecimal and octal) at the beginning of an alphanumeric string into an integer. Hexadecimal characters must begin with "0x"; octal characters must begin with "0". Strtol Examples Decimal: If cell A2 contains the text string "123ABC", then the function Strtol(A2) would return the integer value 123.000. Hexadecimal: If cell A2 contains the text string "0x123", Strtol(A2) would return the integer value 291.000. "0x12AB" = 4,779.000, since "A" and "B" are hexadecimal characters. "0x12GB" = 18.000, since "G" is not a hexadecimal character. Octal: If cell A2 contains the text string "0123ABC", Strtol(A2) would return the integer value 83.000.
Substitute (SrcText, OldText, NewText, [Instance]) | Returns a text string constructed by substituting NewText for each occurrence of OldText within the SrcText. If an optional Instance index is specified, then only that instance of the text will be replaced. Substitute Example If cell A2 contains the text string "ABC123123", cell A3 contains the string "123", and cell A4 contains the string "abc", then Substitute(A2,A3,A4,1) replaces the second instance of "123" in SrcText and returns the string "ABC123abc".
Token (Text, Delimiter, Instance) | Returns the text located at the specified Instance index (occurrence) of a delimited string. The Delimiter must be enclosed in double quotes (" ") in the function arguments. Token Example If cell A2 contains the comma delimited string "A,B,C,1,2,3" then Token(A2,",",2) would return "C" because C is at index 2 in the text.
Trim (Text) | Returns Text stripped of leading and trailing spaces, leaving only a single space between words (if any). All control characters (Carriage Return, Line Feed, etc.) are also stripped. Trim Example If cell A2 contains the string " ABC 123 ", then Trim(A2) would return the string "ABC 123".
Upper (Text) | Returns Text converted to uppercase. Upper Example If cell A2 contains the alphanumeric string, "abc123", then the function Upper(A2) would return the string "ABC123".
Value (NumericText) | Returns the floating point value of NumericText. Subsequent non-numeric characters are ignored. Value Example If cell A2 contains the numeric string "123.4", then the function Value(A2) would return the floating point value 123.400.