XLib


Download
Installation
X-VBA Home

Array
=AverageHigh
=AverageLow
=CountUnique
=IsInArray
=Large
=Reverse
=Small
=Sort
=SumHigh
=SumLow

Color
=Hex2Hsl
=Hex2Rgb
=Hsl2Hex
=Hsl2Rgb
=Rgb2Hex
=Rgb2Hsl
=Rgb2Hsv

DateTime
=DaysOfMonth
=MonthName2
=Quarter
=TimeConverter
=WeekOfMonth
=WeekdayName2

Environment
=ComputerName
=OS
=UserDomain
=UserName

File
=CountFiles
=CountFilesAndFolders
=CountFolders
=CurrentFilePath
=FileCreationTime
=FileDrive
=FileExtension
=FileFolder
=FileLastModifiedTime
=FileName
=FileSize
=FileType
=GetActivePath
=GetActivePathAndName
=GetFileNameByNumber
=PathJoin
=PathSeparator
=ReadFile
=WriteFile

Math
=Ceil
=Floor
=InterpolateNumber
=InterpolatePercent
=Max
=Min
=ModFloat

Meta
=XlibCredits
=XlibDocumentation
=XlibVersion

Network
=Http
=ParseHtmlString
=SimpleHttp

Random
=BigRandBetween
=RandBetween
=RandBetweens
=RandBool
=RandomRange
=RandomSample

Regex
=RegexReplace
=RegexSearch
=RegexTest

StringManipulation
=CamelCase
=Capitalize
=CompanyCase
=CountLowercaseCharacters
=CountUppercaseCharacters
=CountWords
=DedentText
=EliteCase
=Formatter
=InSplit
=IndentText
=KebabCase
=LeftFind
=LeftSearch
=LeftSplit
=RemoveCharacters
=Repeat
=ReverseText
=ReverseWords
=RightFind
=RightSearch
=RightSplit
=ScrambleCase
=ShortenText
=SplitText
=Substr
=SubstrFind
=SubstrSearch
=TextJoin
=TrimChar
=TrimLeft
=TrimRight
=Zfill

StringMetrics
=Damerau
=Hamming
=Levenshtein

Utilities
=BigDec2Hex
=BigHex
=Dec2Hex
=Hex2Dec
=HideText
=HtmlEscape
=HtmlUnescape
=JavaScript
=Jsonify
=Len2
=SpeakText
=UuidFour

Validators
=CreditCardName
=FormatCreditCard
=IsCreditCard
=IsEmail
=IsIPFour
=IsMacAddress
=IsPhone
=IsUrl

StringManipulation Module

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


=CamelCase(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

  • =CamelCase("Hello World") -> "helloWorld"
  • =CamelCase("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"

=CompanyCase(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

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

=CountLowercaseCharacters(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

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

=CountUppercaseCharacters(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

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

=CountWords(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

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

=DedentText(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

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

=EliteCase(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

  • =EliteCase("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"

=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 "-"

=IndentText(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

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

=KebabCase(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

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

=LeftFind(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 =LeftSearch().

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

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

=LeftSearch(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 =FIND() function, this function is NOT case-sensitive (it's case-insensitive). For a case-sensitive version of this function, see =LeftFind().

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

  • =LeftSearch("Hello World", "r") -> "Hello Wo"
  • =LeftSearch("Hello World", "R") -> "Hello Wo"

=LeftSplit(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

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

=RemoveCharacters(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

  • =RemoveCharacters("Hello World", "l") -> "Heo Word"
  • =RemoveCharacters("Hello World", "lo") -> "He Wrd"
  • =RemoveCharacters("Hello World", "l", "o") -> "He Wrd"
  • =RemoveCharacters("Hello World", "lod") -> "He Wr"
  • =RemoveCharacters("Two Three Four", "f", "t") -> "Two Three Four"; Nothing is replaced since this function is case sensitive
  • =RemoveCharacters("Two Three Four", "F", "T") -> "wo hree our"

=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) -> "=========="

=ReverseText(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

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

=ReverseWords(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

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

=RightFind(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 =RightSearch().

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

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

=RightSearch(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 =FIND() function, this function is NOT case-sensitive (it's case-insensitive). For a case-sensitive version of this function, see =RightFind().

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

  • =RightSearch("Hello World", "o") -> "rld"
  • =RightSearch("Hello World", "O") -> "rld"

=RightSplit(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

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

=ScrambleCase(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

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

=ShortenText(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

  • =ShortenText("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
  • =ShortenText("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
  • =ShortenText("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
  • =ShortenText("Hello World One Two Three", 15, "-->") -> "Hello World -->"; A new placeholder is used
  • =ShortenText("Hello_World_One_Two_Three", 15, "-->", "_") -> "Hello_World_-->"; A new placeholder andd delimiter is used

=SplitText(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

  • =SplitText("Hello World", 1) -> "Hello"
  • =SplitText("Hello World", 2) -> "World"
  • =SplitText("One Two Three", 2) -> "Two"
  • =SplitText("One-Two-Three", 2, "-") -> "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"

=SubstrFind(string1, RightFindString, 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. SubstrFind is case-sensitive. For case-insensitive version, see SubstrSearch

Arguments

{String}: string1   ➔   is the string that we will get a substring from

{String}: RightFindString   ➔   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

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

=SubstrSearch(string1, RightFindString, 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. SubstrSearch is case-insensitive. For case-sensitive version, see SubstrFind

Arguments

{String}: string1   ➔   is the string that we will get a substring from

{String}: RightFindString   ➔   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

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

=TextJoin(stringArray, [delimiterCharacter], [ignoreEmptyCellsFlag])

This function takes a range of cells and combines all the text together, optionally allowing a character delimiter between all the combined strings, and optionally allowing blank cells to be ignored when combining the text. Finally note that this function is very similar to the TEXTJOIN function available in Excel 2019, and thus is a polyfill for that function for earlier versions of Excel.

Arguments

{Variant}: stringArray   ➔   is the range with all the strings we want to combine

[(Optional) {String}: delimiterCharacter]   ➔   is an optional character that will be used as the delimiter between the combined text. By default, no delimiter character will be used.

[(Optional) {Boolean}: ignoreEmptyCellsFlag]   ➔   if set to TRUE will skip combining empty cells into the combined string, and is useful when specifying a delimiter so that the delimiter does not repeat for empty cells.


Returns

{String}: Returns a new combined string containing the strings in the range delimited by the delimiter character.


Examples

  • =TextJoin(A1:A3) -> "123"; Where A1:A3 contains ["1", "2", "3"]
  • =TextJoin(A1:A3, "--") -> "1--2--3"; Where A1:A3 contains ["1", "2", "3"]
  • =TextJoin(A1:A3, "--") -> "1----3"; Where A1:A3 contains ["1", "", "3"]
  • =TextJoin(A1:A3, "-") -> "1--3"; Where A1:A3 contains ["1", "", "3"]
  • =TextJoin(A1:A3, "-", TRUE) -> "1-3"; Where A1:A3 contains ["1", "", "3"]

=TrimChar(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

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

=TrimLeft(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

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

=TrimRight(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

  • =TrimRight(" Hello World ") -> " Hello World"
  • =TrimRight("---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"