Standard Library GitHub ↗
Ready-made Excel functions written in Lua. Use them directly in your add-in or adapt them as a starting point. The xllify Assistant and Claude Code both have an index of these functions and will use them wherever possible. See also: built-in globals reference.
Conversion 2 functions
DECIMAL_TO_FRACTION
Converts a decimal number to a readable fraction string
| Parameter | Type | Description |
|---|---|---|
value |
number |
The decimal value to convert |
max_denominator |
number |
The maximum denominator to use (default: 100) |
value |
number |
The decimal value to convert |
max_denominator |
number |
Maximum denominator (default: 100) |
Returns: string - The fraction string (e.g. "3/4")
Examples
DECIMAL_TO_FRACTION(0.75) → "3/4"DECIMAL_TO_FRACTION(0.333) → "1/3"DECIMAL_TO_FRACTION(1.5) → "3/2"
FRACTION_TO_DECIMAL
Parses a fraction string and returns its decimal value
| Parameter | Type | Description |
|---|---|---|
fraction |
string |
The fraction string (e.g. "3/4", "1/3", "7/2") |
fraction |
string |
The fraction string (e.g. "3/4") |
Returns: number - The decimal value of the fraction
Examples
FRACTION_TO_DECIMAL("3/4") → 0.75FRACTION_TO_DECIMAL("1/3") → 0.3333FRACTION_TO_DECIMAL("7/2") → 3.5Date 9 functions
ADD_MONTHS
Adds N calendar months to a date, snapping to end-of-month when needed
| Parameter | Type | Description |
|---|---|---|
date |
number |
The starting date (Excel serial number) |
months |
number |
The number of months to add (can be negative) |
date |
number |
The starting date (Excel serial number) |
months |
number |
The number of months to add |
Returns: number - The resulting date as an Excel serial number
Examples
ADD_MONTHS(44927, 1) → 44955 (Feb 2023 → Mar 2023)ADD_MONTHS(44958, -1) → 44927 (Mar 31 → Feb 28, end-of-month snap)
ADD_YEARS
Adds N years to a date, handling Feb 29 leap year snapping correctly
| Parameter | Type | Description |
|---|---|---|
date |
number |
The starting date (Excel serial number) |
years |
number |
The number of years to add (can be negative) |
date |
number |
The starting date (Excel serial number) |
years |
number |
The number of years to add |
Returns: number - The resulting date as an Excel serial number
Examples
ADD_YEARS(44927, 1) → 45292 (Jan 31 2023 → Jan 31 2024)ADD_YEARS(44956, 1) → 45322 (Feb 28 2023 → Feb 28 2024, not Feb 29)
DATE_DIFF_LABEL
Returns a human-readable time difference label ("3 days ago", "in 2 months")
| Parameter | Type | Description |
|---|---|---|
from_date |
number |
The reference date (Excel serial number) |
to_date |
number |
The target date (Excel serial number) |
from_date |
number |
The reference date |
to_date |
number |
The target date |
Returns: string - A human-readable label describing the difference
Examples
DATE_DIFF_LABEL(44927, 44930) → "in 3 days"DATE_DIFF_LABEL(44930, 44927) → "3 days ago"DATE_DIFF_LABEL(44927, 44927) → "today"
FISCAL_QUARTER
Returns the fiscal quarter number given a date and a fiscal year start month
| Parameter | Type | Description |
|---|---|---|
date |
number |
The date (Excel serial number) |
fiscal_start_month |
number |
The month the fiscal year starts (1=Jan, 4=Apr, 7=Jul, 10=Oct) |
date |
number |
The date (Excel serial number) |
fiscal_start_month |
number |
Month the fiscal year starts (1–12) |
Returns: number - The fiscal quarter number (1–4)
Examples
FISCAL_QUARTER(44927, 4) → 3 (Jan 2023 in fiscal year starting Apr is Q3)FISCAL_QUARTER(44927, 1) → 1 (Jan 2023 in calendar year is Q1)FISCAL_QUARTER(44682, 7) → 1 (Jul 2022 is Q1 in Jul fiscal year)
IS_BUSINESS_DAY
Returns TRUE if a date falls on a Monday through Friday
| Parameter | Type | Description |
|---|---|---|
date |
number |
The date (Excel serial number) |
date |
number |
The date (Excel serial number) |
Returns: boolean - TRUE if the date is Monday–Friday
Examples
IS_BUSINESS_DAY(44927) → trueIS_BUSINESS_DAY(44928) → false
IS_LEAP_YEAR
Returns TRUE if a year number is a leap year
| Parameter | Type | Description |
|---|---|---|
year |
number |
The year to check |
year |
number |
The year to check |
Returns: boolean - TRUE if the year is a leap year
Examples
IS_LEAP_YEAR(2024) → trueIS_LEAP_YEAR(2023) → falseIS_LEAP_YEAR(1900) → falseIS_LEAP_YEAR(2000) → true
START_OF_MONTH
Returns the Excel serial date of the first day of the month containing a date
| Parameter | Type | Description |
|---|---|---|
date |
number |
The date (Excel serial number) |
date |
number |
The date (Excel serial number) |
Returns: number - The Excel serial date of the first day of that month
Examples
START_OF_MONTH(44958) → 44928 (Mar 31 2023 → Mar 1 2023)START_OF_MONTH(44927) → 44927 (Jan 31 2023 → Jan 1 2023 — if on the 1st, returns itself)
WEEK_NUMBER_ISO
Returns the ISO 8601 week number for a date (weeks start on Monday, week 1 contains the first Thursday)
| Parameter | Type | Description |
|---|---|---|
date |
number |
The date (Excel serial number) |
date |
number |
The date (Excel serial number) |
Returns: number - The ISO 8601 week number (1–53)
Examples
WEEK_NUMBER_ISO(44927) → 5WEEK_NUMBER_ISO(45291) → 52WEEK_NUMBER_ISO(44928) → 1
WORKDAYS_BETWEEN
Counts Mon–Fri working days between two dates (exclusive of both endpoints)
| Parameter | Type | Description |
|---|---|---|
start_date |
number |
The start date (Excel serial number, exclusive) |
end_date |
number |
The end date (Excel serial number, exclusive) |
start_date |
number |
The start date (exclusive) |
end_date |
number |
The end date (exclusive) |
Returns: number - The number of Mon–Fri working days between the two dates
Examples
WORKDAYS_BETWEEN(44927, 44934) → 5WORKDAYS_BETWEEN(44927, 44928) → 0Financial 7 functions
ANNUALISE_RETURN
Converts a total return over N days to an annualised rate
| Parameter | Type | Description |
|---|---|---|
total_return |
number |
The total return as a decimal (e.g. 0.15 for 15%) |
days |
number |
The number of days the return was earned over |
total_return |
number |
Total return as a decimal (e.g. 0.15) |
days |
number |
Number of days the return was earned over |
Returns: number - The annualised rate as a decimal
Examples
ANNUALISE_RETURN(0.1, 180) → 0.2071ANNUALISE_RETURN(0.05, 365) → 0.05ANNUALISE_RETURN(0.2, 730) → 0.0954
BREAK_EVEN_UNITS
Calculates the number of units needed to break even
| Parameter | Type | Description |
|---|---|---|
fixed_costs |
number |
Total fixed costs |
price_per_unit |
number |
Selling price per unit |
variable_cost_per_unit |
number |
Variable cost per unit |
fixed_costs |
number |
Total fixed costs |
price_per_unit |
number |
Selling price per unit |
variable_cost_per_unit |
number |
Variable cost per unit |
Returns: number - Units needed to break even
Examples
BREAK_EVEN_UNITS(10000, 50, 30) → 500BREAK_EVEN_UNITS(5000, 20, 12) → 625
COMPOUND_INTEREST
Calculates the total value after compound interest: A = P(1 + r/n)^(nt)
| Parameter | Type | Description |
|---|---|---|
principal |
number |
The initial investment amount |
rate |
number |
The annual interest rate as a decimal (e.g. 0.05 for 5%) |
years |
number |
The number of years |
compounds_per_year |
number |
How many times interest compounds per year (default: 12) |
principal |
number |
The initial investment |
rate |
number |
Annual interest rate as a decimal |
years |
number |
Number of years |
compounds_per_year |
number |
Compounding frequency per year (default: 12) |
Returns: number - The total value after compounding
Examples
COMPOUND_INTEREST(1000, 0.05, 10) → 1647.01COMPOUND_INTEREST(1000, 0.05, 10, 1) → 1628.89COMPOUND_INTEREST(5000, 0.03, 20, 12) → 9070.09
DISCOUNT_FACTOR
Calculates the present value discount factor for a rate and number of periods
| Parameter | Type | Description |
|---|---|---|
rate |
number |
The discount rate per period as a decimal (e.g. 0.1 for 10%) |
periods |
number |
The number of periods |
rate |
number |
Discount rate per period (e.g. 0.1 for 10%) |
periods |
number |
Number of periods |
Returns: number - The discount factor (between 0 and 1)
Examples
DISCOUNT_FACTOR(0.1, 1) → 0.9091DISCOUNT_FACTOR(0.1, 5) → 0.6209DISCOUNT_FACTOR(0.05, 10) → 0.6139
DRAWDOWN
Calculates per-period drawdown from peak for a range of portfolio values
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of portfolio values |
values |
any |
The range of portfolio values |
Returns: any - A column of drawdown values (negative decimals, e.g. -0.1 = 10% below peak)
Examples
DRAWDOWN({100, 110, 105, 115, 100}) → {0; 0; -0.0455; 0; -0.1304}
RULE_OF_72
Approximates the number of years to double an investment at a given annual rate
| Parameter | Type | Description |
|---|---|---|
rate |
number |
The annual interest rate as a decimal (e.g. 0.06 for 6%) |
rate |
number |
Annual rate as a decimal (e.g. 0.06 for 6%) |
Returns: number - Approximate years to double the investment
Examples
RULE_OF_72(0.06) → 12RULE_OF_72(0.09) → 8RULE_OF_72(0.12) → 6
SHARPE_RATIO
Calculates the Sharpe ratio given a range of returns and a risk-free rate
| Parameter | Type | Description |
|---|---|---|
returns |
any |
The range of periodic returns (as decimals) |
risk_free_rate |
number |
The risk-free rate per period as a decimal (e.g. 0.0001 daily) |
returns |
any |
Range of periodic returns as decimals |
risk_free_rate |
number |
Risk-free rate per period as a decimal |
Returns: number - The Sharpe ratio
Examples
SHARPE_RATIO({0.01, 0.02, -0.005, 0.015, 0.008}, 0.0001) → 1.32Logic 4 functions
ALL_OF
Returns TRUE if all values in a range satisfy a condition string (e.g. ">0", "=yes")
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of values to test |
condition |
string |
The condition string (e.g. ">0", "<=100", "=active", "<>") |
values |
any |
The range of values to test |
condition |
string |
Condition string e.g. ">0", "=yes", "<>" |
Returns: boolean - TRUE if all values satisfy the condition
Examples
ALL_OF({1, 2, 3, 4, 5}, ">0") → trueALL_OF({1, -2, 3}, ">0") → falseALL_OF({"yes","yes","yes"}, "=yes") → true
ANY_OF
Returns TRUE if any value in a range satisfies a condition string (e.g. ">0", "=yes")
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of values to test |
condition |
string |
The condition string (e.g. ">0", "<=100", "=active", "<>") |
values |
any |
The range of values to test |
condition |
string |
Condition string e.g. ">0", "=yes", "<>" |
Returns: boolean - TRUE if at least one value satisfies the condition
Examples
ANY_OF({-1, -2, 3}, ">0") → trueANY_OF({-1, -2, -3}, ">0") → falseANY_OF({"yes","no","maybe"}, "=yes") → true
DEFAULT_IF_ERROR
Returns a default value when the input is an error, blank, empty string, or zero
| Parameter | Type | Description |
|---|---|---|
value |
any |
The value to check |
default |
any |
The default value to return if value is error, blank, empty, or zero |
value |
any |
The value to check |
default |
any |
The fallback value |
Returns: any - The original value, or default if it is error/blank/empty/zero
Examples
DEFAULT_IF_ERROR("", "N/A") → "N/A"DEFAULT_IF_ERROR(0, 1) → 1DEFAULT_IF_ERROR("hello", "N/A") → "hello"DEFAULT_IF_ERROR(42, 0) → 42
SWITCH_MAP
Maps an input value to an output value using a paired key/value range
| Parameter | Type | Description |
|---|---|---|
value |
any |
The input value to look up |
keys |
any |
The range of keys to match against |
values |
any |
The range of corresponding output values |
default |
any |
The value to return if no match is found (optional) |
value |
any |
The input value to look up |
keys |
any |
The range of keys |
values |
any |
The range of output values |
default |
any |
Value to return if no match (optional) |
Returns: any - The mapped output value, or default if not found
Examples
SWITCH_MAP("B", {"A","B","C"}, {"Alpha","Beta","Gamma"}) → "Beta"SWITCH_MAP(2, {1,2,3}, {"low","mid","high"}) → "mid"SWITCH_MAP("X", {"A","B"}, {1,2}, "unknown") → "unknown"Lookup 4 functions
CLOSEST
Returns the value in a range that is numerically closest to a target
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of numbers to search |
target |
number |
The target value to find the closest to |
values |
any |
The range of numbers |
target |
number |
The target value |
Returns: number - The value in the range closest to the target
Examples
CLOSEST({1, 5, 10, 15, 20}, 12) → 10CLOSEST({100, 200, 300}, 250) → 200CLOSEST({3, 7, 11}, 9) → 7
COLLECT_UNIQUE
Returns all unique matching values from a range where a criteria range matches, as a spilled array
| Parameter | Type | Description |
|---|---|---|
criteria_range |
any |
The range to check against the criteria |
criteria |
any |
The value to match |
return_range |
any |
The range to return values from |
criteria_range |
any |
The range to check against criteria |
criteria |
any |
The value to match |
return_range |
any |
The range to return values from |
Returns: any - A column array of unique matching values
Examples
COLLECT_UNIQUE({"A","B","A","C"}, "A", {10, 10, 30, 40}) → {10; 30}COLLECT_UNIQUE({1,1,2,1}, 1, {"x","x","y","z"}) → {"x"; "z"}
FUZZY
Finds the closest matching string using fuzzy text matching
| Parameter | Type | Description |
|---|---|---|
needle |
string |
The string to search for |
haystack |
string |
The string to compare against |
needle |
string |
The string to search for |
haystack |
string |
The string to compare against |
Returns: number - Similarity score from 0 to 1 (1 = exact match)
Examples
FUZZY("hello", "hello") → 1FUZZY("hello", "helo") → 0.8FUZZY("apple", "orange") → 0.16...FUZZY("test", "TEST") → 1
NTH_MATCH
Returns the Nth matching value from a return range where a criteria range equals a value
| Parameter | Type | Description |
|---|---|---|
criteria_range |
any |
The range to check |
criteria |
any |
The value to match |
return_range |
any |
The range to return values from |
n |
number |
Which match to return (1 = first, 2 = second, etc.) |
criteria_range |
any |
The range to check |
criteria |
any |
The value to match |
return_range |
any |
The range to return values from |
n |
number |
Which match to return (1-based) |
Returns: any - The Nth matching value, or empty string if not found
Examples
NTH_MATCH({"A","B","A","A"}, "A", {10,20,30,40}, 2) → 30NTH_MATCH({"x","y","x"}, "x", {"a","b","c"}, 1) → "a"Math 11 functions
CLAMP
Constrains a value between a minimum and maximum
| Parameter | Type | Description |
|---|---|---|
value |
number |
The value to constrain |
min_val |
number |
The minimum allowed value |
max_val |
number |
The maximum allowed value |
value |
number |
The value to constrain |
min_val |
number |
Minimum allowed value |
max_val |
number |
Maximum allowed value |
Returns: number - The clamped value
Examples
CLAMP(15, 0, 10) → 10CLAMP(-5, 0, 10) → 0CLAMP(5, 0, 10) → 5
COUNT_IF_BETWEEN
Counts values in a range that fall between min and max (inclusive)
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range to count from |
min_val |
number |
The minimum value (inclusive) |
max_val |
number |
The maximum value (inclusive) |
values |
any |
The range to count from |
min_val |
number |
Minimum value (inclusive) |
max_val |
number |
Maximum value (inclusive) |
Returns: number - The count of values in the range
Examples
COUNT_IF_BETWEEN({1, 5, 10, 15, 20}, 5, 15) → 3COUNT_IF_BETWEEN({1, 2, 3, 4, 5}, 2, 4) → 3
GEOMETRIC_MEAN
Calculates the geometric mean of a range of positive numbers
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of positive numbers |
values |
any |
The range of positive numbers |
Returns: number - The geometric mean
Examples
GEOMETRIC_MEAN({1, 2, 4, 8}) → 2.828GEOMETRIC_MEAN({2, 8}) → 4GEOMETRIC_MEAN({100, 110, 121}) → 110
HARMONIC_MEAN
Calculates the harmonic mean of a range of positive numbers
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of positive numbers |
values |
any |
The range of positive numbers |
Returns: number - The harmonic mean
Examples
HARMONIC_MEAN({1, 2, 4}) → 1.714HARMONIC_MEAN({60, 40}) → 48HARMONIC_MEAN({2, 2, 2}) → 2
MODE_RANGE
Returns the most frequently occurring value in a range, returning the lowest on ties
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of values |
values |
any |
The range of values |
Returns: any - The most frequently occurring value (lowest on ties)
Examples
MODE_RANGE({1, 2, 2, 3, 3}) → 2MODE_RANGE({5, 5, 3, 3, 1}) → 3MODE_RANGE({7, 7, 7, 2}) → 7
NORMALIZE
Calculates the z-score of a value given a mean and standard deviation
| Parameter | Type | Description |
|---|---|---|
value |
number |
The value to normalize |
mean |
number |
The mean of the distribution |
stddev |
number |
The standard deviation of the distribution |
value |
number |
The value to normalize |
mean |
number |
The mean of the distribution |
stddev |
number |
The standard deviation |
Returns: number - The z-score (number of standard deviations from the mean)
Examples
NORMALIZE(70, 60, 10) → 1NORMALIZE(50, 60, 10) → -1NORMALIZE(60, 60, 10) → 0
PERCENTILE_RANK
Returns what percentile a value sits at within a range (0–100)
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of numbers to rank within |
value |
number |
The value to find the percentile rank for |
values |
any |
The range of numbers |
value |
number |
The value to rank |
Returns: number - The percentile rank (0–100)
Examples
PERCENTILE_RANK({1, 2, 3, 4, 5}, 3) → 50PERCENTILE_RANK({10, 20, 30, 40, 50}, 10) → 0PERCENTILE_RANK({10, 20, 30, 40, 50}, 50) → 100
ROUND_TO_MULTIPLE
Rounds a value to the nearest multiple of a given number
| Parameter | Type | Description |
|---|---|---|
value |
number |
The value to round |
multiple |
number |
The multiple to round to (e.g. 0.25, 5, 10) |
value |
number |
The value to round |
multiple |
number |
The multiple to round to |
Returns: number - The value rounded to the nearest multiple
Examples
ROUND_TO_MULTIPLE(7, 5) → 5ROUND_TO_MULTIPLE(8, 5) → 10ROUND_TO_MULTIPLE(0.7, 0.25) → 0.75
RUNNING_TOTAL
Returns the cumulative sum of a range as a spilled column
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of numbers |
values |
any |
The range of numbers |
Returns: any - A column of cumulative sums
Examples
RUNNING_TOTAL({1, 2, 3, 4, 5}) → {1; 3; 6; 10; 15}RUNNING_TOTAL({10, -5, 20}) → {10; 5; 25}
STDDEV_RANGE
Calculates the population standard deviation of a range of values
| Parameter | Type | Description |
|---|---|---|
values |
any |
The range of numbers |
values |
any |
The range of numbers |
Returns: number - The population standard deviation
Examples
STDDEV_RANGE({2, 4, 4, 4, 5, 5, 7, 9}) → 2STDDEV_RANGE({10, 20, 30}) → 8.165
SUM_IF_BETWEEN
Sums values where the corresponding criteria range falls between min and max (inclusive)
| Parameter | Type | Description |
|---|---|---|
criteria_range |
any |
The range to test |
min_val |
number |
The minimum value (inclusive) |
max_val |
number |
The maximum value (inclusive) |
sum_range |
any |
The range of values to sum (defaults to criteria_range if omitted) |
criteria_range |
any |
The range to test |
min_val |
number |
Minimum value (inclusive) |
max_val |
number |
Maximum value (inclusive) |
sum_range |
any |
Range to sum (optional, defaults to criteria_range) |
Returns: number - The sum of matching values
Examples
SUM_IF_BETWEEN({1, 5, 10, 15, 20}, 5, 15) → 30SUM_IF_BETWEEN({1, 2, 3, 4, 5}, 2, 4, {10, 20, 30, 40, 50}) → 90Text 8 functions
EXTRACT_DOMAIN
Extracts the domain from an email address or URL
| Parameter | Type | Description |
|---|---|---|
text |
string |
An email address or URL |
text |
string |
An email address or URL |
Returns: string - The domain (e.g. "acme.com")
Examples
EXTRACT_DOMAIN("user@acme.com") → "acme.com"EXTRACT_DOMAIN("https://www.example.com/path") → "example.com"EXTRACT_DOMAIN("mailto:info@company.org") → "company.org"
FIRST_NAME
Extracts the first word from a full name string
| Parameter | Type | Description |
|---|---|---|
name |
string |
The full name |
name |
string |
The full name |
Returns: string - The first name (first token)
Examples
FIRST_NAME("Jane Ann Smith") → "Jane"FIRST_NAME("John Doe") → "John"FIRST_NAME("Madonna") → "Madonna"
INITIALS
Extracts initials from a full name
| Parameter | Type | Description |
|---|---|---|
name |
string |
The full name |
separator |
string |
The separator between initials (default: ".") |
name |
string |
The full name |
separator |
string |
Separator between initials (default: ".") |
Returns: string - The initials (e.g. "J.A.S.")
Examples
INITIALS("Jane Ann Smith") → "J.A.S."INITIALS("John Doe") → "J.D."INITIALS("Madonna") → "M."INITIALS("Jane Ann Smith", "") → "JAS"
LAST_NAME
Extracts the last word from a full name string
| Parameter | Type | Description |
|---|---|---|
name |
string |
The full name |
name |
string |
The full name |
Returns: string - The last name (last token)
Examples
LAST_NAME("Jane Ann Smith") → "Smith"LAST_NAME("John Doe") → "Doe"LAST_NAME("Madonna") → "Madonna"
REMOVE_ACCENTS
Strips diacritics from accented characters (é→e, ü→u, ñ→n)
| Parameter | Type | Description |
|---|---|---|
text |
string |
The text containing accented characters |
text |
string |
The text to process |
Returns: string - The text with accents removed
Examples
REMOVE_ACCENTS("café") → "cafe"REMOVE_ACCENTS("naïve") → "naive"REMOVE_ACCENTS("Ångström") → "Angstrom"
STRIP_NON_ALPHA
Removes everything except letters and spaces from a string
| Parameter | Type | Description |
|---|---|---|
text |
string |
The text to clean |
text |
string |
The text to clean |
Returns: string - The text with only letters and spaces remaining
Examples
STRIP_NON_ALPHA("Hello, World! 123") → "Hello World "STRIP_NON_ALPHA("user@email.com") → "useremailcom"STRIP_NON_ALPHA("abc-def_ghi") → "abcdefghi"
STRIP_NON_NUMERIC
Removes everything except digits, decimal point, and minus sign from a string
| Parameter | Type | Description |
|---|---|---|
text |
string |
The text to clean |
text |
string |
The text to clean |
Returns: string - The text with only numeric characters remaining
Examples
STRIP_NON_NUMERIC("$1,234.56") → "1234.56"STRIP_NON_NUMERIC("-42.5°C") → "-42.5"STRIP_NON_NUMERIC("phone: 555-1234") → "555-1234"
WRAP_TEXT
Inserts line breaks every N characters at word boundaries
| Parameter | Type | Description |
|---|---|---|
text |
string |
The text to wrap |
width |
number |
The maximum line width in characters |
text |
string |
The text to wrap |
width |
number |
Maximum line width in characters |
Returns: string - The text with line breaks inserted
Examples
WRAP_TEXT("The quick brown fox jumps over the lazy dog", 15) → "The quick brown\nfox jumps over\nthe lazy dog"WRAP_TEXT("Hello world", 20) → "Hello world"Validation 4 functions
IS_BLANK_OR_ZERO
Returns TRUE if a value is blank, an empty string, or zero
| Parameter | Type | Description |
|---|---|---|
value |
any |
The value to check |
value |
any |
The value to check |
Returns: boolean - TRUE if the value is blank, empty string, or zero
Examples
IS_BLANK_OR_ZERO("") → trueIS_BLANK_OR_ZERO(0) → trueIS_BLANK_OR_ZERO("hello") → falseIS_BLANK_OR_ZERO(42) → false
IS_CREDIT_CARD
Validates a credit card number using the Luhn algorithm
| Parameter | Type | Description |
|---|---|---|
value |
any |
The card number (string or number, spaces and dashes ignored) |
value |
any |
The card number to validate |
Returns: boolean - TRUE if the card number passes the Luhn check
Examples
IS_CREDIT_CARD("4111111111111111") → trueIS_CREDIT_CARD("4111111111111112") → falseIS_CREDIT_CARD("5500005555555559") → true
IS_DATE_VALID
Returns TRUE if a value represents a plausible Excel date serial (1900–2200)
| Parameter | Type | Description |
|---|---|---|
value |
any |
The value to check |
value |
any |
The value to check |
Returns: boolean - TRUE if the value is a plausible Excel date serial number
Examples
IS_DATE_VALID(44927) → trueIS_DATE_VALID(0) → falseIS_DATE_VALID(109574) → falseIS_DATE_VALID("hello") → false
IS_JSON
Returns TRUE if a string is valid JSON
| Parameter | Type | Description |
|---|---|---|
value |
any |
The value to check |
value |
any |
The value to check |
Returns: boolean - TRUE if the value is a valid JSON string
Examples
IS_JSON("{\"key\": \"value\"}") → trueIS_JSON("[1, 2, 3]") → trueIS_JSON("not json") → falseIS_JSON("") → false