XPlus


Download
Installation
Changelog
Support
X-VBA Home

Validators
=CREDIT_CARD_NAME
=FORMAT_CREDIT_CARD
=FORMAT_FORMULA
=FORMAT_FRACTION
=FORMAT_PHONE
=IS_CREDIT_CARD
=IS_EMAIL
=IS_IP_FOUR
=IS_MAC_ADDRESS
=IS_PHONE
=IS_URL

Utilities
=ADDIN_EXISTS
=ADDIN_INSTALLED
=AVERAGESHEET
=COUNTERRORALL
=DISPLAY_TEXT
=EVALUATE_FORMULA
=HIDDEN
=HTML_ESCAPE
=HTML_TABLEIFY
=HTML_UNESCAPE
=ISBADERROR
=ISERRORALL
=JAVASCRIPT
=JSONIFY
=MAXSHEET
=MINSHEET
=REFERENCE_EXISTS
=SPEAK_TEXT
=SUMSHEET
=UUID_FOUR

StringMetrics
=DAMERAU
=DAM_STR
=DAM_STR_OPT
=HAMMING
=LEVENSHTEIN
=LEV_STR
=LEV_STR_OPT
=PARTIAL_LOOKUP

StringManipulation
=CAMEL_CASE
=CAPITALIZE
=COMPANY_CASE
=COUNT_LOWERCASE_CHARACTERS
=COUNT_UPPERCASE_CHARACTERS
=COUNT_WORDS
=DEDENT
=ELITE_CASE
=FORMATTER
=INDENT
=INSPLIT
=INSTRING
=KEBAB_CASE
=LEFT_FIND
=LEFT_SEARCH
=LEFT_SPLIT
=REMOVE_CHARACTERS
=REPEAT
=REVERSE_TEXT
=REVERSE_WORDS
=RIGHT_FIND
=RIGHT_SEARCH
=RIGHT_SPLIT
=SCRAMBLE_CASE
=SHORTEN
=SPLIT_TEXT
=SUBSTITUTE_ALL
=SUBSTR
=SUBSTR_FIND
=SUBSTR_SEARCH
=TRIM_CHAR
=TRIM_LEFT
=TRIM_RIGHT
=ZFILL

Regex
=REGEX_REPLACE
=REGEX_SEARCH
=REGEX_TEST

Range
=ALTERNATE_COLUMNS
=ALTERNATE_ROWS
=AVERAGEHIGH
=AVERAGELOW
=AVERAGEN
=COLUMNIFY
=COUNT_UNIQUE
=COUNT_UNIQUE_COLORS
=FIRST_UNIQUE
=INRANGE
=MAXN
=MINN
=REVERSE_RANGE
=ROWIFY
=SORT_RANGE
=SUMHIGH
=SUMLOW
=SUMN

Random
=RANDBETWEENS
=RANDBOOL
=RANDOM_RANGE
=RANDOM_SAMPLE
=RANDOM_SAMPLE_PERCENT

Properties
=RANGE_COLOR
=RANGE_COMMENT
=RANGE_FONT
=RANGE_HEIGHT
=RANGE_HYPERLINK
=RANGE_NAME
=RANGE_NUMBER_FORMAT
=RANGE_WIDTH
=SHEET_CODE_NAME
=SHEET_NAME
=SHEET_TYPE
=WORKBOOK_AUTHOR
=WORKBOOK_CATEGORY
=WORKBOOK_COMMENTS
=WORKBOOK_COMPANY
=WORKBOOK_CREATION_DATE
=WORKBOOK_HYPERLINK_BASE
=WORKBOOK_KEYWORDS
=WORKBOOK_LAST_AUTHOR
=WORKBOOK_LAST_SAVE_TIME
=WORKBOOK_MANAGER
=WORKBOOK_REVISION_NUMBER
=WORKBOOK_SUBJECT
=WORKBOOK_TITLE

Polyfill
=CONCAT_TEXT
=MAX_IF
=MAX_IFS
=MIN_IF
=MIN_IFS
=TEXT_JOIN

Network
=HTTP
=PARSE_HTML_STRING
=SIMPLE_HTTP

Meta
=CREDITS
=DOCUMENTATION
=VERSION

Math
=INTERPOLATE_NUMBER
=INTERPOLATE_PERCENT

File
=COUNT_FILES
=COUNT_FILES_AND_FOLDERS
=COUNT_FOLDERS
=FILE_CREATION_TIME
=FILE_DRIVE
=FILE_EXTENSION
=FILE_FOLDER
=FILE_LAST_MODIFIED_TIME
=FILE_NAME
=FILE_PATH
=FILE_SIZE
=FILE_TYPE
=GET_FILE_NAME
=PATH_JOIN
=READ_FILE
=WRITE_FILE

Environment
=COMPUTER_NAME
=ENVIRONMENT
=OS
=USER_DOMAIN
=USER_NAME

DateTime
=DAYS_OF_MONTH
=MONTH_NAME
=QUARTER
=TIME_CONVERTER
=WEEKDAY_NAME
=WEEK_OF_MONTH

Color
=HEX2HSL
=HEX2RGB
=HSL2HEX
=HSL2RGB
=RGB2HEX
=RGB2HSL
=RGB2HSV

XPlus  

StringManipulation Module

This module contains a set of basic functions for manipulating strings.


=CAMEL_CASE(string1)

This function takes a string and returns the same string in camel case, removing all the spaces.

Arguments

String: string1   --->   is the string that will be camel cased


Returns

String: Returns a new string in camel case, where the first character of the first word is lowercase, and uppercased for all other words


Examples

  • =CAMEL_CASE("Hello World") -> "helloWorld"
  • =CAMEL_CASE("One Two Three") -> "oneTwoThree"

=CAPITALIZE(string1)

This function takes a string and returns the same string with the first character capitalized and all other characters lowercased

Arguments

String: string1   --->   is the string that the capitalization will be performed on


Returns

String: Returns a new string with the first character capitalized and all others lowercased


Examples

  • =CAPITALIZE("hello World") -> "Hello world"

=COMPANY_CASE(string1)

This function takes a string and uses an algorithm to return the string in Company Case. The standard =PROPER() function in Excel will not capitalize company names properly, as it only capitalizes based on space characters, so a name like "j.p. morgan" will be incorrectly formatted as "J.p. Morgan" instead of the correct "J.P. Morgan". Additionally =PROPER() may incorrectly lowercase company abbreviations, such as the last "H" in "GmbH", as =PROPER() returns "Gmbh" instead of the correct "GmbH". This function attempts to adjust for these issues when a string is a company name.

Arguments

String: string1   --->   is the string that will be formatted


Returns

String: Returns the origional string in a Company Case format


Warning!
There is no perfect algorithm for correctly formatting company names, and while this function can give better performance for correct formatting when compared to =PROPER(), if the performance of this function isn't as accurate as one needs, another solution would be to try Partial Lookup functions in the String Metrics Module and compare that to a known list of well formatted company strings.

Examples

  • =COMPANY_CASE("hello world") -> "Hello World"
  • =COMPANY_CASE("x.y.z company & co.") -> "X.Y.Z Company & Co."
  • =COMPANY_CASE("x.y.z plc") -> "X.Y.Z PLC"
  • =COMPANY_CASE("one company gmbh") -> "One Company GmbH"
  • =COMPANY_CASE("three company s. en n.c.") -> "Three Company S. en N.C."
  • =COMPANY_CASE("FOUR COMPANY SPOL S.R.O.") -> "Four Company spol s.r.o."
  • =COMPANY_CASE("five company bvba") -> "Five Company BVBA"

=COUNT_LOWERCASE_CHARACTERS(string1)

This function takes a string and counts the number of lowercase characters in it

Arguments

String: string1   --->   is the string whose characters will be counted


Returns

Integer: Returns the number of lowercase characters in the string


Examples

  • =COUNT_LOWERCASE_CHARACTERS("Hello World") -> 8; As the "ello" and the "orld" are lowercase

=COUNT_UPPERCASE_CHARACTERS(string1)

This function takes a string and counts the number of uppercase characters in it

Arguments

String: string1   --->   is the string whose characters will be counted


Returns

Integer: Returns the number of uppercase characters in the string


Examples

  • =COUNT_UPPERCASE_CHARACTERS("Hello World") -> 2; As the "H" and the "E" are the only 2 uppercase characters in the string

=COUNT_WORDS(string1, [delimiterString])

This function takes a string and returns the number of words in the string

Arguments

String: string1   --->   is the string whose number of words will be counted

[(Optional) String = " ": delimiterString]   --->   is an optional parameter that can be used to specify a different delimiter


Returns

Integer: Returns the number of words in the string


Note
If the number given is higher than the number of words, its possible that the string contains excess whitespace. Try using the =TRIM() function first to remove the excess whitespace

Examples

  • =COUNT_WORDS("Hello World") -> 2
  • =COUNT_WORDS("One Two Three") -> 3
  • =COUNT_WORDS("One-Two-Three", "-") -> 3

=DEDENT(string1)

This function takes a string and dedents all of its lines so that there are no space characters to the left or right of each line

Arguments

String: string1   --->   is the string that will be dedented


Returns

String: Returns the origional string dedented on each line


Note
Unlike the Excel built-in TRIM() function, this function will dedent every single line, so for strings that span multiple lines in a cell, this will dedent all lines.

Examples

  • =DEDENT(" Hello") -> "Hello"

=ELITE_CASE(string1)

This function takes a string and returns the string with characters replaced by similar in appearance numbers

Arguments

String: string1   --->   is the string that will have characters replaced


Returns

String: Returns the string with characters replaced with similar in appearance numbers


Examples

  • =ELITE_CASE("Hello World") -> "H3110 W0r1d"

=FORMATTER(formatString, textArray)

This function takes a formatter string and then an array of ranges or strings, and replaces the format placeholders with the values in the range or strings. The format syntax is "{1} - {2}" where the "{1}" and "{2}" will be replaced with the values given in the text array.

Arguments

String: formatString   --->   is the string that will be used as the format and which will be replaced with the individual strings

Variant: textArray()   --->   are the ranges or strings that will be placed within the slots of the format string


Returns

Variant: Returns a new string with the individual strings in the placeholder slots of the format string


Examples

  • =FORMATTER("Hello {1}", "World") -> "Hello World"
  • =FORMATTER("{1} {2}", "Hello", "World") -> "Hello World"
  • =FORMATTER("{1}.{2}@{3}", "FirstName", "LastName", "email.com") -> "FirstName.LastName@email.com"
  • =FORMATTER("{1}.{2}@{3}", A1:A3) -> "FirstName.LastName@email.com"; where A1="FirstName", A2="LastName", and A3="email.com"
  • =FORMATTER("{1}.{2}@{3}", A1, A2, A3) -> "FirstName.LastName@email.com"; where A1="FirstName", A2="LastName", and A3="email.com"

=INDENT(string1, [indentAmount])

This function takes a string and indents all of its lines by a specified number of space characters (or 4 space characters if left blank)

Arguments

String: string1   --->   is the string that will be indented

[(Optional) Byte = 4: indentAmount]   --->   is the amount of " " characters that will be indented to the left of string1


Returns

String: Returns the origional string indented by a specified number of space characters


Examples

  • =INDENT("Hello") -> " Hello"
  • =INDENT("Hello", 4) -> " Hello"
  • =INDENT("Hello", 3) -> " Hello"
  • =INDENT("Hello", 2) -> " Hello"
  • =INDENT("Hello", 1) -> " Hello"

=INSPLIT(string1, splitString, [delimiterCharacter])

This function takes a search string and checks if it exists within a larger string that is split by a delimiter character.

Arguments

String: string1   --->   is the string that will be checked if it exists within the splitString after the split

String: splitString   --->   is the string that will be split and of which string1 will be searched in

[(Optional) String = " ": delimiterCharacter]   --->   is the character that will be used as the delimiter for the split. By default this is the space character " "


Returns

Boolean: Returns TRUE if string1 is found in splitString after the split occurs


Examples

  • =INSPLIT("Hello", "Hello World One Two Three") -> TRUE; Since "Hello" is found within the searchString after being split
  • =INSPLIT("NotInString", "Hello World One Two Three") -> FALSE; Since "NotInString" is not found within the searchString after being split
  • =INSPLIT("Hello", "Hello-World-One-Two-Three", "-") -> TRUE; Since "Hello" is found and since the delimiter is set to "-"

=INSTRING(string1, stringArray)

This function takes a string, and then any number of substrings, and will check if any of the substrings can be found within the string

Arguments

String: string1   --->   is the string that will be checked against the substrings

Variant: stringArray()   --->   is any number of substrings that will be used to check if they exist within the string


Returns

Variant: Returns Boolean TRUE if any of the substrings can be found within the string, and FALSE if none are found


Examples

  • =INSTRING("112 - 312 - 221 - 132", "111", "222", "333") -> FALSE; None of the substrings are found
  • =INSTRING("123 - 222 - 122 - 311", "111", "222", "333") -> TRUE; "222" is found
  • =INSTRING("222 - 322 - 233 - 232", "111", "222", "333") -> TRUE; "222" is found
  • =INSTRING("312 - 131 - 123 - 333", "111", "222", "333") -> TRUE; "333" is found
  • =INSTRING("212 - 232 - 213 - 323", "111", "222", "333") -> FALSE
  • =INSTRING("111 - 212 - 222 - 333", "111", "222", "333") -> TRUE; "111", "222", and "333" are found

=KEBAB_CASE(string1)

This function takes a string and returns the same string in kebab case.

Arguments

String: string1   --->   is the string that will be kebab cased


Returns

String: Returns a new string in kebab case, where all letters are lowercase and seperated by a "-" character


Examples

  • =KEBAB_CASE("Hello World") -> "hello-world"
  • =KEBAB_CASE("One Two Three") -> "one-two-three"

=LEFT_FIND(string1, searchString)

This function takes a string and a search string, and returns a string with all characters to the left of the first search string found within string1. Similar to Excel's built-in =SEARCH() function, this function is case-sensitive. For a case-insensitive version of this function, see =LEFT_SEARCH().

Arguments

String: string1   --->   is the string that will be searched

String: searchString   --->   is the string that will be used to search within string1


Returns

String: Returns a new string with all characters to the left of the first search string within string1


Examples

  • =LEFT_FIND("Hello World", "r") -> "Hello Wo"
  • =LEFT_FIND("Hello World", "R") -> "#VALUE!"; Since string1 does not contain "R" in it.


=LEFT_SPLIT(string1, numberOfSplit, [delimiterCharacter])

This function takes a string, splits it based on a delimiter, and returns all characters to the left of the specified position of the split.

Arguments

String: string1   --->   is the string that will be split to get a substring

Integer: numberOfSplit   --->   is the number of the location within the split that we will get all characters to the left of

[(Optional) String = " ": delimiterCharacter]   --->   is the delimiter that will be used for the split. By default, the delimiter will be the space character " "


Returns

String: Returns all characters to the left of the number of the split


Examples

  • =LEFT_SPLIT("Hello World One Two Three", 1) -> "Hello"
  • =LEFT_SPLIT("Hello World One Two Three", 2) -> "Hello World"
  • =LEFT_SPLIT("Hello World One Two Three", 3) -> "Hello World One"
  • =LEFT_SPLIT("Hello World One Two Three", 10) -> "Hello World One Two Three"
  • =LEFT_SPLIT("Hello-World-One-Two-Three", 2, "-") -> "Hello-World"

=REMOVE_CHARACTERS(string1, removedCharacters)

This function takes a string and either another string or multiple strings and removes all characters from the first string that are in the second string.

Arguments

String: string1   --->   is the string that will have characters removed

Variant: removedCharacters()   --->   is an array of strings that will be removed from string1


Returns

Variant: Returns the origional string with characters removed


Note
This function is case sensitive. If you want to remove the "H" from "Hello World" you would need to use "H" as a removed character, not "h".

Examples

  • =REMOVE_CHARACTERS("Hello World", "l") -> "Heo Word"
  • =REMOVE_CHARACTERS("Hello World", "lo") -> "He Wrd"
  • =REMOVE_CHARACTERS("Hello World", "l", "o") -> "He Wrd"
  • =REMOVE_CHARACTERS("Hello World", "lod") -> "He Wr"
  • =REMOVE_CHARACTERS("One Two Three", "o", "t") -> "One Two Three"; Nothing is replaced since this function is case sensitive
  • =REMOVE_CHARACTERS("One Two Three", "O", "T") -> "ne wo hree"

=REPEAT(string1, numberOfRepeats)

This function repeats string1 based on the number of repeats specified in the second argument

Arguments

String: string1   --->   is the string that will be repeated

Integer: numberOfRepeats   --->   is the number of times string1 will be repeated


Returns

String: Returns a string repeated multiple times based on the numberOfRepeats


Examples

  • =REPEAT("Hello", 2) -> HelloHello"
  • =REPEAT("=", 10) -> "=========="

=REVERSE_TEXT(string1)

This function takes a string and reverses all the characters in it so that the returned string is backwards

Arguments

String: string1   --->   is the string that will be reversed


Returns

String: Returns the origional string in reverse


Examples

  • =REVERSE_TEXT("Hello World") -> "dlroW olleH"

=REVERSE_WORDS(string1, [delimiterCharacter])

This function takes a string and reverses all the words in it so that the returned string's words are backwards. By default, this function uses the space character as a delimiter, but you can optionally specify a different delimiter.

Arguments

String: string1   --->   is the string whose words will be reversed

[(Optional) String = " ": delimiterCharacter]   --->   is the delimiter that will be used, with the default being " "


Returns

String: Returns the origional string with it's words reversed


Examples

  • =REVERSE_WORDS("Hello World") -> "World Hello"
  • =REVERSE_WORDS("One Two Three") -> "Three Two One"
  • =REVERSE_WORDS("One-Two-Three", "-") -> "Three-Two-One"

=RIGHT_FIND(string1, searchString)

This function takes a string and a search string, and returns a string with all characters to the right of the last search string found within string 1. Similar to Excel's built-in =SEARCH() function, this function is case-sensitive. For a case-insensitive version of this function, see =RIGHT_SEARCH().

Arguments

String: string1   --->   is the string that will be searched

String: searchString   --->   is the string that will be used to search within string1


Returns

String: Returns a new string with all characters to the right of the last search string within string1


Examples

  • =RIGHT_FIND("Hello World", "o") -> "rld"
  • =RIGHT_FIND("Hello World", "O") -> "#VALUE!"; Since string1 does not contain "O" in it.


=RIGHT_SPLIT(string1, numberOfSplit, [delimiterCharacter])

This function takes a string, splits it based on a delimiter, and returns all characters to the right of the specified position of the split.

Arguments

String: string1   --->   is the string that will be split to get a substring

Integer: numberOfSplit   --->   is the number of the location within the split that we will get all characters to the right of

[(Optional) String = " ": delimiterCharacter]   --->   is the delimiter that will be used for the split. By default, the delimiter will be the space character " "


Returns

String: Returns all characters to the right of the number of the split


Examples

  • =RIGHT_SPLIT("Hello World One Two Three", 1) -> "Three"
  • =RIGHT_SPLIT("Hello World One Two Three", 2) -> "Two Three"
  • =RIGHT_SPLIT("Hello World One Two Three", 3) -> "One Two Three"
  • =RIGHT_SPLIT("Hello World One Two Three", 10) -> "Hello World One Two Three"
  • =RIGHT_SPLIT("Hello-World-One-Two-Three", 2, "-") -> "Two-Three"

=SCRAMBLE_CASE(string1)

This function takes a string scrambles the case on each character in the string

Arguments

String: string1   --->   is the string whose character's cases will be scrambled


Returns

String: Returns the origional string with cases scrambled


Examples

  • =SCRAMBLE_CASE("Hello World") -> "helLo WORlD"
  • =SCRAMBLE_CASE("Hello World") -> "HElLo WorLD"
  • =SCRAMBLE_CASE("Hello World") -> "hELlo WOrLd"

=SHORTEN(string1, [shortenWidth], [placeholderText], [delimiterCharacter])

This function takes a string and shortens it with placeholder text so that it is no longer in length than the specified width.

Arguments

String: string1   --->   is the string that will be shortened

[(Optional) Integer = 80: shortenWidth]   --->   is the max width of the string. By default this is set to 80

[(Optional) String = "[...]": placeholderText]   --->   is the text that will be placed at the end of the string if it is longer than the shortenWidth. By default this placeholder string is "[...]

[(Optional) String = " ": delimiterCharacter]   --->   is the character that will be used as the word delimiter. By default this is the space character " "


Returns

String: Returns a shortened string with placeholder text if it is longer than the shorten width


Examples

  • =SHORTEN("Hello World One Two Three", 20) -> "Hello World [...]"; Only the first two words and the placeholder will result in a string that is less than or equal to 20 in length
  • =SHORTEN("Hello World One Two Three", 15) -> "Hello [...]"; Only the first word and the placeholder will result in a string that is less than or equal to 15 in length
  • =SHORTEN("Hello World One Two Three") -> "Hello World One Two Three"; Since this string is shorter than the default 80 shorten width value, no placeholder will be used and the string wont be shortened
  • =SHORTEN("Hello World One Two Three", 15, "-->") -> "Hello World -->"; A new placeholder is used
  • =SHORTEN("Hello_World_One_Two_Three", 15, "-->", "_") -> "Hello_World_-->"; A new placeholder andd delimiter is used

=SPLIT_TEXT(string1, substringNumber, [delimiterString])

This function takes a string and a number, splits the string by the space characters, and returns the substring in the position of the number specified in the second argument.

Arguments

String: string1   --->   is the string that will be split and a substring returned

Integer: substringNumber   --->   is the number of the substring that will be chosen

[(Optional) String = " ": delimiterString]   --->   is an optional parameter that can be used to specify a different delimiter


Returns

String: Returns a substring of the split text in the location specified


Examples

  • =SPLIT_TEXT("Hello World", 1) -> "Hello"
  • =SPLIT_TEXT("Hello World", 2) -> "World"
  • =SPLIT_TEXT("One Two Three", 2) -> "Two"
  • =SPLIT_TEXT("One-Two-Three", 2, "-") -> "Two"

=SUBSTITUTE_ALL(string1, oldTextRange, newTextRange)

This function takes a string, and old text range, and a new text range, and replaces all the strings in the old text range with the ones in the new text range.

Arguments

String: string1   --->   is the string that will have substrings replaced

Range: oldTextRange   --->   is a range containing the text that will be replaced by the text in the newTextRange

Range: newTextRange   --->   is the replacement range


Returns

String: Returns the origional string with all the replacements from the two ranges


Examples

  • =SUBSTITUTE_ALL("Hello World", A1:A2, B1:B2) -> "One Two"; Where A1:A2=["Hello", "World"] and B1:B2=["One", "Two"]

=SUBSTR(string1, startCharacterNumber, endCharacterNumber)

This function takes a string and a starting character number and ending character number, and returns the substring between these two numbers. The total number of characters returned will be endCharacterNumber - startCharacterNumber.

Arguments

String: string1   --->   is the string that we will get a substring from

Integer: startCharacterNumber   --->   is the character number of the start of the substring, with 1 being the first character in the string

Integer: endCharacterNumber   --->   is the character number of the end of the substring


Returns

String: Returns a substring between the two numbers.


Examples

  • =SUBSTR("Hello World", 2, 6) -> "ello"

=SUBSTR_FIND(string1, leftSearchString, rightSearchString, [noninclusiveFlag])

This function takes a string and a left string and right string, and returns a substring between those two strings. The left string will find the first matching string starting from the left, and the right string will find the first matching string starting from the right. Finally, and optional final parameter can be set to TRUE to make the substring noninclusive of the two searched strings. SUBSTR_FIND is case-sensitive. For case-insensitive version, see SUBSTR_SEARCH

Arguments

String: string1   --->   is the string that we will get a substring from

String: leftSearchString   --->   is the string that will be searched from the left

String: rightSearchString   --->   is the string that will be searched from the right

[(Optional) Boolean: noninclusiveFlag]   --->   is an optional parameter that if set to TRUE will result in the substring not including the left and right searched characters


Returns

String: Returns a substring between the two strings.


Examples

  • =SUBSTR_FIND("Hello World", "e", "o") -> "ello Wo"
  • =SUBSTR_FIND("Hello World", "e", "o", TRUE) -> "llo W"
  • =SUBSTR_FIND("One Two Three", "ne ", " Thr") -> "ne Two Thr"
  • =SUBSTR_FIND("One Two Three", "NE ", " THR") -> "#VALUE!"; Since SUBSTR_FIND() is case-sensitive
  • =SUBSTR_FIND("One Two Three", "ne ", " Thr", TRUE) -> "Two"
  • =SUBSTR_FIND("Country Code: +51; Area Code: 315; Phone Number: 762-5929;", "Area Code: ", "; Phone", TRUE) -> 315
  • =SUBSTR_FIND("Country Code: +313; Area Code: 423; Phone Number: 284-2468;", "Area Code: ", "; Phone", TRUE) -> 423
  • =SUBSTR_FIND("Country Code: +171; Area Code: 629; Phone Number: 731-5456;", "Area Code: ", "; Phone", TRUE) -> 629


=TRIM_CHAR(string1, [trimCharacter])

This function takes a string trims characters to the left and right of the string, similar to Excel's Built-in TRIM() function, except that an optional different character can be used for the trim.

Arguments

String: string1   --->   is the string that will be trimmed

[(Optional) String = " ": trimCharacter]   --->   is an optional character that will be trimmed from the string. By default, this character will be the space character " "


Returns

String: Returns the origional string with characters trimmed from the left and right


Note
This function currently supports only single characters for trimming

Examples

  • =TRIM_CHAR(" Hello World ") -> "Hello World"
  • =TRIM_CHAR("---Hello World---", "-") -> "Hello World"

=TRIM_LEFT(string1, [trimCharacter])

This function takes a string trims characters to the left of the string, similar to Excel's Built-in TRIM() function, except that an optional different character can be used for the trim.

Arguments

String: string1   --->   is the string that will be trimmed

[(Optional) String = " ": trimCharacter]   --->   is an optional character that will be trimmed from the string. By default, this character will be the space character " "


Returns

String: Returns the origional string with characters trimmed from the left only


Note
This function currently supports only single characters for trimming

Examples

  • =TRIM_LEFT(" Hello World ") -> "Hello World "
  • =TRIM_LEFT("---Hello World---", "-") -> "Hello World---"

=TRIM_RIGHT(string1, [trimCharacter])

This function takes a string trims characters to the right of the string, similar to Excel's Built-in TRIM() function, except that an optional different character can be used for the trim.

Arguments

String: string1   --->   is the string that will be trimmed

[(Optional) String = " ": trimCharacter]   --->   is an optional character that will be trimmed from the string. By default, this character will be the space character " "


Returns

String: Returns the origional string with characters trimmed from the right only


Note
This function currently supports only single characters for trimming

Examples

  • =TRIM_RIGHT(" Hello World ") -> " Hello World"
  • =TRIM_RIGHT("---Hello World---", "-") -> "---Hello World"

=ZFILL(string1, fillLength, [fillCharacter], [rightToLeftFlag])

This function pads zeros to the left of a string until the string is at least the length of the fill length. Optional parameters can be used to pad with a different character than 0, and to pad from right to left instead of from the default left to right.

Arguments

String: string1   --->   is the string that will be filled

Byte: fillLength   --->   is the length that string1 will be padded to. In cases where string1 is of greater length than this argument, no padding will occur.

[(Optional) String = "0": fillCharacter]   --->   is an optional string that will change the character that will be padded with

[(Optional) Boolean: rightToLeftFlag]   --->   is a Boolean parameter that if set to TRUE will result in padding from right to leftt instead of left to right


Returns

String: Returns a new padded string of the length of specified by fillLength at minimum


Examples

  • =ZFILL(123, 5) -> "00123"
  • =ZFILL(5678, 5) -> "05678"
  • =ZFILL(12345678, 5) -> "12345678"
  • =ZFILL(123, 5, "X") -> "XX123"
  • =ZFILL(123, 5, "X", TRUE) -> "123XX"