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  

Range Module

This module contains a set of functions for manipulating and working with ranges of cells.


=ALTERNATE_COLUMNS(rangeGrid, outputRange)

This function takes a grid of cells, and converts the grid into a single columns where the values of the grid alternate between the columns in the grid.

Arguments

Range: rangeGrid   --->   is a grid of cells

Range: outputRange   --->   is the column that will be populated with the data from the grid


Returns

Variant: Returns one of the values from the grid in alternating column order


Examples

  • =ALTERNATE_COLUMNS($A$1:$B$2, $C$1:$C$4) -> "A1 Value"; Where this function is the 1st cell in the column
  • =ALTERNATE_COLUMNS($A$1:$B$2, $C$1:$C$4) -> "B1 Value"; Where this function is the 2nd cell in the column
  • =ALTERNATE_COLUMNS($A$1:$B$2, $C$1:$C$4) -> "A2 Value"; Where this function is the 3rd cell in the column
  • =ALTERNATE_COLUMNS($A$1:$B$2, $C$1:$C$4) -> "B2 Value"; Where this function is the 4th cell in the column

=ALTERNATE_ROWS(rangeGrid, outputRange)

This function takes a grid of cells, and converts the grid into a single columns where the values of the grid alternate between the rows in the grid.

Arguments

Range: rangeGrid   --->   is a grid of cells

Range: outputRange   --->   is the column that will be populated with the data from the grid


Returns

Variant: Returns one of the values from the grid in alternating row order


Examples

  • =ALTERNATE_ROWS($A$1:$B$2, $C$1:$C$4) -> "A1 Value"; Where this function is the 1st cell in the column
  • =ALTERNATE_ROWS($A$1:$B$2, $C$1:$C$4) -> "A2 Value"; Where this function is the 2nd cell in the column
  • =ALTERNATE_ROWS($A$1:$B$2, $C$1:$C$4) -> "B1 Value"; Where this function is the 3rd cell in the column
  • =ALTERNATE_ROWS($A$1:$B$2, $C$1:$C$4) -> "B2 Value"; Where this function is the 4th cell in the column

=AVERAGEHIGH(rangeArray, numberAveraged)

This function returns the average of the top values of the number specified in the second argument. For example, if the second argument is 3, only the top 3 values will be averaged

Arguments

Range: rangeArray   --->   is the range that will be averaged

Integer: numberAveraged   --->   is the number of the top values that will be averaged


Returns

Variant: Returns the average of the top numbers specified


Examples

  • =AVERAGEHIGH(A1:A4, 2) -> 3.5; Where A1=1, A2=2, A3=3, A4=4; 4 and 3 are averaged to 3.5
  • =AVERAGEHIGH(A1:A4, 3) -> 3; Where A1=1, A2=2, A3=3, A4=4; 4, 3, and 2 are averaged to 3

=AVERAGELOW(rangeArray, numberAveraged)

This function returns the average of the bottom values of the number specified in the second argument. For example, if the second argument is 3, only the bottom 3 values will be averaged

Arguments

Range: rangeArray   --->   is the range that will be averaged

Integer: numberAveraged   --->   is the number of the bottom values that will be averaged


Returns

Variant: Returns the average of the bottom numbers specified


Examples

  • =AVERAGELOW(A1:A4, 2) -> 1.5; Where A1=1, A2=2, A3=3, A4=4; 1 and 2 are averaged as 1.5
  • =AVERAGELOW(A1:A4, 3) -> 2; Where A1=1, A2=2, A3=3, A4=4; 1, 2, and 3 are averaged to 2

=AVERAGEN(rangeArray, nthNumber, [startAtBeginningFlag])

This function averages up every Nth value of a range. For example, if you have a range that is 4 cells long, and set the nthNumber to 2, then only the 2nd and 4th cell value will be averaged up. Optionally, a third parameter can be set to TRUE, and if so the averaging will start at the first cell. For example, for 4 cells in a range and for the nthNumber set to 2, the 1st and 3rd cell will be averaged.

Arguments

Range: rangeArray   --->   is the range that will be averaged up

Integer: nthNumber   --->   is the number which will determine which cells are averaged

[(Optional) Boolean: startAtBeginningFlag]   --->   is an optional value that if set to TRUE will make the average start at the first cell instead of at the nth cell


Returns

Variant: Returns the average of the nth cells


Note
If the range chosen is more than 1 cell in width, the averaging will occur in left-to-right and then top-to-bottom order

Examples

  • =AVERAGEN(A1:A4, 2) -> 3; Where A1=1, A2=2, A3=3, A4=4
  • =AVERAGEN(A1:A4, 2, TRUE) -> 2; Where A1=1, A2=2, A3=3, A4=4

=COLUMNIFY(columnRangeArray, rowRangeArray)

This function takes 2 ranges, a column range which will be filled in with data in the row range, allowing for easily converting a row of data into a column of data

Arguments

Range: columnRangeArray   --->   is a range that will be populated with data from a rowRangeArray

Range: rowRangeArray   --->   is a range that will be used to populate the columnRangeArray


Returns

Variant: Returns the value at the same location in the rowRangeArray


Examples

  • =COLUMNIFY(A1:A2, B1:C1) -> "B"; Where this function resides in cell A1 and where B1="B" and C1="C"
  • =COLUMNIFY(A1:A2, B1:C1) -> "C"; Where this function resides in cell A2 and where B1="B" and C1="C"

=COUNT_UNIQUE(rangeArray)

This function counts the number of unique occurances of values within a range or multiple ranges

Arguments

Variant: rangeArray()   --->   is the group of cells we are counting the unique values of


Returns

Variant: Returns the number of unique values


Examples

  • =COUNT_UNIQUE(A1:A5) -> 3; Where A1-A5 contains ["A", "A", "B", "A", "C"]

=COUNT_UNIQUE_COLORS(rangeArray)

This function counts the number of unique background colors of the cells in a range or multiple ranges

Arguments

Variant: rangeArray()   --->   is a range or multiple ranges whose colors will be counted


Returns

Variant: Returns the number of unique background colors of all the cells


Examples

  • =COUNT_UNIQUE_COLORS(A1:A3) -> 3; Where all the cells have a unique background color
  • =COUNT_UNIQUE_COLORS(A1:A3) -> 2; Where A1 and A2 have the same background color

=FIRST_UNIQUE(range1, rangeArray)

This function takes a single cell and an large range of cells and returns TRUE if the cell selected is the first unique value in the larger array of cells, and returns FALSE if it is not the first unique value.

Arguments

Range: range1   --->   is the range we want to check if the value is the first unique value in the rangeArray

Range: rangeArray   --->   is the group of cells we are checking to see if range1 is the first unique occurrence in the rangeArray


Returns

Boolean: Returns TRUE if the cell selected is the first unique value in the range array, and FALSE if it isn't


Examples

  • =FIRST_UNIQUE(A1, $A$1:$A$10) -> TRUE, where A1 is the first unique occurrence of the word "Hello" in the range array
  • =FIRST_UNIQUE(A5, $A$1:$A$10) -> FALSE, where A5 is the second unique occurrence of the word "Hello" in the range array

=INRANGE(valueOrRange, searchRange)

This function takes a range or a value, and a second range, and returns TRUE if the first range or value is within the second range. Otherwise it returns FALSE

Arguments

Variant: valueOrRange   --->   is the range or value that will be checked if it exists in the search range

Range: searchRange   --->   is the range that contains values that will be checked in the second range


Returns

Boolean: Returns TRUE if the first value or range contains a value in the second range


Examples

  • =INRANGE(A1:A3, B1:B3) -> TRUE; Where A1="One", A2="Two", A3="Three", B1="Five", B2="Six", and B3="One"; TRUE since "One" occurs in both ranges
  • =INRANGE(A1:A3, B1:B3) -> TRUE; Where A1="One", A2="Two", A3="Three", B1="Five", B2="Six", and B3="Seven"; FALSE since the ranges have no values in common
  • =INRANGE("Five", B1:B3) -> TRUE; B1="Five", B2="Six", and B3="Seven"; TRUE since "Five" is in the search range

=MAXN(rangeArray, nthNumber, [startAtBeginningFlag])

This function maxes up every Nth value of a range. For example, if you have a range that is 4 cells long, and set the nthNumber to 2, then only the 2nd and 4th cell value will be maxed up. Optionally, a third parameter can be set to TRUE, and if so the maxing will start at the first cell. For example, for 4 cells in a range and for the nthNumber set to 2, the 1st and 3rd cell will be maxed.

Arguments

Range: rangeArray   --->   is the range that will be maxed up

Integer: nthNumber   --->   is the number which will determine which cells are maxed

[(Optional) Boolean: startAtBeginningFlag]   --->   is an optional value that if set to TRUE will make the max start at the first cell instead of at the nth cell


Returns

Variant: Returns the max of the nth cells


Note
If the range chosen is more than 1 cell in width, the maxing will occur in left-to-right and then top-to-bottom order

Examples

  • =MAXN(A1:A4, 2) -> 4; Where A1=1, A2=2, A3=3, A4=4
  • =MAXN(A1:A4, 2, TRUE) -> 3; Where A1=1, A2=2, A3=3, A4=4

=MINN(rangeArray, nthNumber, [startAtBeginningFlag])

This function mins up every Nth value of a range. For example, if you have a range that is 4 cells long, and set the nthNumber to 2, then only the 2nd and 4th cell value will be minned up. Optionally, a third parameter can be set to TRUE, and if so the minning will start at the first cell. For example, for 4 cells in a range and for the nthNumber set to 2, the 1st and 3rd cell will be minned.

Arguments

Range: rangeArray   --->   is the range that will be minned up

Integer: nthNumber   --->   is the number which will determine which cells are minned

[(Optional) Boolean: startAtBeginningFlag]   --->   is an optional value that if set to TRUE will make the min start at the first cell instead of at the nth cell


Returns

Variant: Returns the min of the nth cells


Note
If the range chosen is more than 1 cell in width, the minning will occur in left-to-right and then top-to-bottom order

Examples

  • =MINN(A1:A4, 2) -> 2; Where A1=1, A2=2, A3=3, A4=4
  • =MINN(A1:A4, 2, TRUE) -> 1; Where A1=1, A2=2, A3=3, A4=4

=REVERSE_RANGE(range1, rangeArray)

This function takes a single cell and a large range of cells and reverses all values in the range.

Arguments

Range: range1   --->   is the range we want to be reversed in order of the rangeArray

Range: rangeArray   --->   is the group of cells we are reversing the order of


Returns

Variant: Returns the value of the cell in the reversed position


Examples

  • =REVERSE_RANGE(A1, $A$1:$A$3) -> "C", where A1="A", A2="B", A3="C"
  • =REVERSE_RANGE(A2, $A$1:$A$3) -> "B", where A1="A", A2="B", A3="C"
  • =REVERSE_RANGE(A3, $A$1:$A$3) -> "A", where A1="A", A2="B", A3="C"

=ROWIFY(rowRangeArray, columnRangeArray)

This function takes 2 ranges, a row range which will be filled in with data in the column range, allowing for easily converting a column of data into a row of data

Arguments

Range: rowRangeArray   --->   is a range that will be populated with data from a columnRangeArray

Range: columnRangeArray   --->   is a range that will be used to populate the rowRangeArray


Returns

Variant: Returns the value at the same location in the columnRangeArray


Examples

  • =ROWIFY(B1:C1, A1:A2) -> "A1"; Where this function resides in cell B1 and where A1="A1" and A2="A2"
  • =ROWIFY(B1:C1, A1:A2) -> "A2"; Where this function resides in cell C1 and where A1="A1" and A2="A2"

=SORT_RANGE(range1, rangeArray, [descendingFlag])

This function takes a single cell and a large range of cells and sorts the cells in ascending or descending order.

Arguments

Range: range1   --->   is the range containing a single cell that we want to sort

Range: rangeArray   --->   is the group of cells we are sorting

[(Optional) Boolean: descendingFlag]   --->   is a Boolean value that if set to TRUE will set the sort to Descending


Returns

Variant: Returns the value of the cell sorted


Examples

  • =SORT_RANGE(A1, $A$1:$A$4) -> 1, where A1="3", A2="1", A3="4", A4="2"
  • =SORT_RANGE(A1, $A$1:$A$4, TRUE) -> 4, where A1="3", A2="1", A3="4", A4="2"

=SUMHIGH(rangeArray, numberSummed)

This function returns the sum of the top values of the number specified in the second argument. For example, if the second argument is 3, only the top 3 values will be summed

Arguments

Range: rangeArray   --->   is the range that will be summed

Integer: numberSummed   --->   is the number of the top values that will be summed


Returns

Variant: Returns the sum of the top numbers specified


Examples

  • =SUMHIGH(A1:A4, 2) -> 7; Where A1=1, A2=2, A3=3, A4=4; 4 and 3 are summed to 7
  • =SUMHIGH(A1:A4, 3) -> 9; Where A1=1, A2=2, A3=3, A4=4; 4, 3, and 2 are summed to 9

=SUMLOW(rangeArray, numberSummed)

This function returns the sum of the bottom values of the number specified in the second argument. For example, if the second argument is 3, only the bottom 3 values will be summed

Arguments

Range: rangeArray   --->   is the range that will be summed

Integer: numberSummed   --->   is the number of the bottom values that will be summed


Returns

Variant: Returns the sum of the bottom numbers specified


Examples

  • =SUMLOW(A1:A4, 2) -> 3; Where A1=1, A2=2, A3=3, A4=4; 1 and 2 are summed to 3
  • =SUMLOW(A1:A4, 3) -> 6; Where A1=1, A2=2, A3=3, A4=4; 1, 2, and 3 are summed to 6

=SUMN(rangeArray, nthNumber, [startAtBeginningFlag])

This function sums up every Nth value of a range. For example, if you have a range that is 4 cells long, and set the nthNumber to 2, then only the 2nd and 4th cell value will be summed up. Optionally, a third parameter can be set to TRUE, and if so the summing will start at the first cell. For example, for 4 cells in a range and for the nthNumber set to 2, the 1st and 3rd cell will be summed.

Arguments

Range: rangeArray   --->   is the range that will be summed up

Integer: nthNumber   --->   is the number which will determine which cells are summed

[(Optional) Boolean: startAtBeginningFlag]   --->   is an optional value that if set to TRUE will make the sum start at the first cell instead of at the nth cell


Returns

Variant: Returns the sum of the nth cells


Note
If the range chosen is more than 1 cell in width, the summing will occur in left-to-right and then top-to-bottom order

Examples

  • =SUMN(A1:A4, 2) -> 6; Where A1=1, A2=2, A3=3, A4=4
  • =SUMN(A1:A4, 2, TRUE) -> 4; Where A1=1, A2=2, A3=3, A4=4