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  

Validators Module

This module contains a set of functions for validating some commonly used string, such as validators for email addresses and phone numbers.


=CREDIT_CARD_NAME(string1)

This function checks if a string is a valid credit card from one of the major card issuing companies, and then returns the name of the credit card name. This function assumes no spaces or hyphens (if you have card numbers with spaces or hyphens you can remove these using =SUBSTITUTE("-", "") function.

Arguments

String: string1   --->   is the credit card string


Returns

String: Returns the name of the credit card. Currently supports these cards: Visa, MasterCard, Discover, Amex, Diners, JCB


Examples

  • =CREDIT_CARD_NAME("5111567856785678") -> "MasterCard"; This is a valid Mastercard number
  • =CREDIT_CARD_NAME("not_a_card_number") -> #VALUE!

=FORMAT_CREDIT_CARD(string1)

This function checks if a string is a valid credit card, and if it is formats it in a more readable way. The format used is XXXX-XXXX-XXXX-XXXX.

Arguments

String: string1   --->   is credit card number


Returns

String: Returns a string formatted as a more readable credit card number


Examples

  • =FORMAT_CREDIT_CARD("5111567856785678") -> "5111-5678-5678-5678"

=FORMAT_FORMULA(range1)

This function formats a formula in a more readable way by breaking up the formula into multiple lines, making it easier to debug larger formulas.

Arguments

Range: range1   --->   is the range with the formula we want to format


Returns

String: Returns a formula formatted in a more readable format


Examples

  • =FORMAT_FORMULA(A1) -> A multiline formula with indentation. See example below:

=FORMAT_FRACTION(decimal1)

This function takes a decimal number and formats it as a close rounded fraction.

Arguments

Double: decimal1   --->   is decimal number that will be formatted


Returns

String: Returns a string of a decimal formatted as a fraction


Examples

  • =FORMAT_FRACTION(".33") -> "1/3"
  • =FORMAT_FRACTION(".35") -> "1/3"
  • =FORMAT_FRACTION(".37") -> "3/8"
  • =FORMAT_FRACTION(".7") -> "2/3"
  • =FORMAT_FRACTION("2.5") -> "2 1/2"

=FORMAT_PHONE(string1)

This function checks if a string is a phone number and if it is, formats the phone number as a more readable string.

Arguments

String: string1   --->   is a phone number


Returns

String: Returns a string formatted as a more readable phone number


Examples

  • =FORMAT_PHONE("123 456 7890") -> "(123) 456-7890"

=IS_CREDIT_CARD(string1)

This function checks if a string is a valid credit card from one of the major card issuing companies.

Arguments

String: string1   --->   is the string we are checking if its a valid credit card number


Returns

Boolean: Returns TRUE if the string is a valid credit card number, and FALSE if its invalid. Currently supports these cards: Visa, MasterCard, Discover, Amex, Diners, JCB


Examples

  • =IS_CREDIT_CARD("5111567856785678") -> TRUE; This is a valid Mastercard number
  • =IS_CREDIT_CARD("511156785678567") -> FALSE; Not enough digits
  • =IS_CREDIT_CARD("9999999999999999") -> FALSE; Enough digits, but not a valid card number
  • =IS_CREDIT_CARD("Hello World") -> FALSE

=IS_EMAIL(string1)

This function checks if a string is a valid email address.

Arguments

String: string1   --->   is the string we are checking if its a valid email


Returns

Boolean: Returns TRUE if the string is a valid email, and FALSE if its invalid


Examples

  • =IS_EMAIL("JohnDoe@testmail.com") -> TRUE
  • =IS_EMAIL("JohnDoe@test/mail.com") -> FALSE
  • =IS_EMAIL("not_an_email_address") -> FALSE

=IS_IP_FOUR(string1)

This function checks if a string is a valid IPv4 address.

Arguments

String: string1   --->   is the string we are checking if its a valid IPv4 address


Returns

Boolean: Returns TRUE if the string is a valid IPv4, and FALSE if its invalid


Examples

  • =IS_IP_FOUR("0.0.0.0") -> TRUE
  • =IS_IP_FOUR("100.100.100.100") -> TRUE
  • =IS_IP_FOUR("255.255.255.255") -> TRUE
  • =IS_IP_FOUR("255.255.255.256") -> FALSE; as the final 256 makes the address outside of the bounds of IPv4
  • =IS_IP_FOUR("0.0.0") -> FALSE; as the fourth octet is missing

=IS_MAC_ADDRESS(string1)

This function checks if a string is a valid 48-bit Mac Address.

Arguments

String: string1   --->   is the string we are checking if its a valid 48-bit Mac Address


Returns

Boolean: Returns TRUE if the string is a valid 48-bit Mac Address, and FALSE if its invalid


Examples

  • =IS_MAC_ADDRESS("00:25:96:12:34:56") -> TRUE
  • =IS_MAC_ADDRESS("FF:FF:FF:FF:FF:FF") -> TRUE
  • =IS_MAC_ADDRESS("00-25-96-12-34-56") -> TRUE
  • =IS_MAC_ADDRESS("123.789.abc.DEF") -> TRUE
  • =IS_MAC_ADDRESS("Not A Mac Address") -> FALSE
  • =IS_MAC_ADDRESS("FF:FF:FF:FF:FF:FH") -> FALSE; the H at the end is not a valid Hex number

=IS_PHONE(string1)

This function checks if a string is a phone number is valid.

Arguments

String: string1   --->   is the string we are checking if its a valid phone number


Returns

Boolean: Returns TRUE if the string is a valid phone number, and FALSE if its invalid


Examples

  • =IS_PHONE("123 456 7890") -> TRUE
  • =IS_PHONE("1234567890") -> TRUE
  • =IS_PHONE("1-234-567-890") -> FALSE; Not enough digits
  • =IS_PHONE("1-234-567-8905") -> TRUE
  • =IS_PHONE("+1-234-567-890") -> FALSE; Not enough digits
  • =IS_PHONE("+1-234-567-8905") -> TRUE
  • =IS_PHONE("+1-(234)-567-8905") -> TRUE
  • =IS_PHONE("+1 (234) 567 8905") -> TRUE
  • =IS_PHONE("1(234)5678905") -> TRUE
  • =IS_PHONE("123-456-789") -> FALSE; Not enough digits
  • =IS_PHONE("Hello World") -> FALSE; Not a phone number

=IS_URL(string1)

This function checks if a string is a valid URL address.

Arguments

String: string1   --->   is the string we are checking if its a valid URL


Returns

Boolean: Returns TRUE if the string is a valid URL, and FALSE if its invalid


Examples

  • =IS_URL("https://www.wikipedia.org/") -> TRUE
  • =IS_URL("http://www.wikipedia.org/") -> TRUE
  • =IS_URL("hello_world") -> FALSE