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

StringMetrics Module

This module contains a set of functions for performing fuzzy string matches. It can be useful when you have 2 columns containing text that is close but not 100% the same. However, since the functions in this module only perform fuzzy matches, there is no guarantee that there will be 100% accuracy in the matches. However, for small groups of string where each string is very different than the other (such as a small group of fairly dissimilar names), these functions can be highly accurate. Finally, some of the functions in this Module will take a long time to calculate for large numbers of cells, as the number of calculations for some functions will grow exponentially, but for small sets of data (such as 100 strings to compare), these functions perform fairly quickly.


=Damerau(string1, string2)

This function takes two strings of any length and calculates the Damerau-Levenshtein Distance between them. Damerau-Levenshtein Distance differs from Levenshtein Distance in that it includes an additional operation, called Transpositions, which occurs when two adjacent characters are swapped. Thus, Damerau-Levenshtein Distance calculates the number of Insertions, Deletions, Substitutions, and Transpositons needed to convert string1 into string2. As a result, this function is good when it is likely that spelling errors have occured between two string where the error is simply a transposition of 2 adjacent characters.

Arguments

{String}: string1   ➔   is the first string

{String}: string2   ➔   is the second string that will be compared to the first string


Returns

{Integer}: Returns an integer of the Damerau-Levenshtein Distance between two string


Examples

  • =Damerau("Cat", "Bat") -> 1; Since all that is needed is 1 change (changing the "B" in Bat to "C")
  • =Damerau("Cat", "Ca") -> 1; Since only one Insertion needs to occur (adding a "t" at the end)
  • =Damerau("Cat", "Cta") -> 1; Since the "t" and "a" can be transposed as they are adjacent to each other. Notice how LEVENSHTEIN("Cat","Cta")=2 but DAMERAU("Cat","Cta")=1

=Hamming(string1, string2)

This function takes two strings of the same length and calculates the Hamming Distance between them. Hamming Distance measures how close two strings are by checking how many Substitutions are needed to turn one string into the other. Lower numbers mean the strings are closer than high numbers.

Arguments

{String}: string1   ➔   is the first string

{String}: string2   ➔   is the second string that will be compared to the first string


Returns

{Integer}: Returns an integer of the Hamming Distance between two string


Examples

  • =Hamming("Cat", "Bat") -> 1; Since all that is needed is 1 change (changing the "B" in Bat to "C")
  • =Hamming("Cat", "Bag") -> 2; 2 changes are needed, changing the "B" to "C" and the "g" to "t"
  • =Hamming("Cat", "Dog") -> 3; Every single character needs to be substituted in this case

=Levenshtein(string1, string2)

This function takes two strings of any length and calculates the Levenshtein Distance between them. Levenshtein Distance measures how close two strings are by checking how many Insertions, Deletions, or Substitutions are needed to turn one string into the other. Lower numbers mean the strings are closer than high numbers. Unlike Hamming Distance, Levenshtein Distance works for strings of any length and includes 2 more operations. However, calculation time will be slower than Hamming Distance for same length strings, so if you know the two strings are the same length, its preferred to use Hamming Distance.

Arguments

{String}: string1   ➔   is the first string

{String}: string2   ➔   is the second string that will be compared to the first string


Returns

{Long}: Returns an integer of the Levenshtein Distance between two string


Examples

  • =Levenshtein("Cat", "Bat") -> 1; Since all that is needed is 1 change (changing the "B" in Bat to "C")
  • =Levenshtein("Cat", "Ca") -> 1; Since only one Insertion needs to occur (adding a "t" at the end)
  • =Levenshtein("Cat", "Cta") -> 2; Since the "t" in "Cta" needs to be substituted into an "a", and the final character "a" needs to be substituted into a "t"