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  

Polyfill Module

This module contains a set of functions that act as polyfills for functions in later versions of Excel. For example, MAXIF() is available in some later versions of Excel, but a user may not have access to this function if they are using an older version of Excel. In this case, this module adds a polyfill called MAX_IF() which works very similar to the MAXIF() function


=CONCAT_TEXT(rangeOrStringArray)

This function takes multiple ranges and strings and concatenates all of them together. It is a polyfill for the CONCAT() function.

Arguments

Variant: rangeOrStringArray()   --->   is any number of strings and ranges that will be concatenated together


Returns

Variant: Returns a concatenated string


Examples

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

=MAX_IF(maxRange, criteriaRange, criteriaValue)

This function takes a max range, a criteria range, and then a criteria value, and finds the maximum value given the criteria

Arguments

Range: maxRange   --->   is the range that the max value will be chosen from given the criteria

Range: criteriaRange   --->   is the range that will be checked against the criteria

Variant: criteriaValue   --->   is the value of the criteria. The criteria allowed is similar to the SUMIF() function


Returns

Variant: Returns the max value that passes the criteria


Examples

  • =MAX_IF(A1:A3, B1:B3, 10) -> 2; Where A1:A3=[1, 2, 3] and B1:B3=[20, 10, 5]
  • =MAX_IF(A1:A3, B1:B3, "<10") -> 3; Where A1:A3=[1, 2, 3] and B1:B3=[20, 10, 5]

=MAX_IFS(maxRange, criteraRangeAndCriteria)

This function takes a max range, and then any number or criteria ranges and criteria values, and returns the max value in the max range conditional on the values passing the criteria. It uses very similar criteria and syntax to the Excel Built-in SUMIFS().

Arguments

Range: maxRange   --->   is the range that the max value will be chosen from given the criteria

Variant: criteraRangeAndCriteria()   --->   is either a range that will be checked against a criteria, or the criteria. These values should alternate between criteria range and criteria value


Returns

Variant: Returns the max value that passes all the criteria


Examples

  • =MAX_IFS(A1:A3, B1:B3, ">=10", C1:C3, "A") -> 2; Where A1:A3=[1, 2, 3], B1:B3=[20, 10, 5], and C1:C3=["A", "A", "C"]

=MIN_IF(minRange, criteriaRange, criteriaValue)

This function takes a min range, a criteria range, and then a criteria value, and finds the minimum value given the criteria

Arguments

Range: minRange   --->   is the range that the min value will be chosen from given the criteria

Range: criteriaRange   --->   is the range that will be checked against the criteria

Variant: criteriaValue   --->   is the value of the criteria. The criteria allowed is similar to the SUMIF() function


Returns

Variant: Returns the min value that passes the criteria


Examples

  • =MIN_IF(A1:A3, B1:B3, 5) -> 3; Where A1:A3=[1, 2, 3] and B1:B3=[20, 10, 5]
  • =MIN_IF(A1:A3, B1:B3, "<=10") -> 2; Where A1:A3=[1, 2, 3] and B1:B3=[20, 10, 5]

=MIN_IFS(minRange, criteraRangeAndCriteria)

This function takes a min range, and then any number or criteria ranges and criteria values, and returns the min value in the min range conditional on the values passing the criteria. It uses very similar criteria and syntax to the Excel Built-in SUMIFS().

Arguments

Range: minRange   --->   is the range that the min value will be chosen from given the criteria

Variant: criteraRangeAndCriteria()   --->   is either a range that will be checked against a criteria, or the criteria. These values should alternate between criteria range and criteria value


Returns

Variant: Returns the min value that passes all the criteria


Examples

  • =MIN_IFS(A1:A3, B1:B3, ">=10", C1:C3, "A") -> 1; Where A1:A3=[1, 2, 3], B1:B3=[20, 10, 5], and C1:C3=["A", "A", "C"]

=TEXT_JOIN(rangeArray, [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

Range: rangeArray   --->   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

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