- CHAR
- CLEAN
- CODE
- CONCATENATE
- DOLLAR
- EXACT
- FIND
- FINDB
- FIXED
- LEFT
- LEN
- LOWER
- MID
- NUMBERVALUE
- PROPER
- REPLACE
- REPT
- RIGHT
- SEARCH
- SUBSTITUTE
- T
- TEXT
- TRIM
- UNICHAR
- UNICODE
- UPPER
- VALUE
- TEXTJOIN
- CONCAT
- LEFTB
- RIGHTB
- MIDB
- LENB
- REPLACEB
- ASC
- JIS
- SEARCHB
- UNIQUE
- TEXTAFTER
- TEXTBEFORE
- VALUETOTEXT
- ARRAYTOTEXT
- TOCOL
- TOROW
- CHOOSECOLS
- CHOOSEROWS
- CHOOSEROWS
- SEQUENCE
- TEXTSPLIT
- TAKE
- DROP
- EXPAND
- WRAPROWS
- WRAPCOLS
- SORT
- SORTBY
- FILTER
- BAHTTEXT
Contact Support
Text Formulas in WinForms Calculation Engine (Calculate)
3 Dec 202424 minutes to read
CHAR
The Char
function returns the character whose number code is defined in the parameter.
Syntax:
CHAR(number)
where:
- number is the numeric value to retrieve the character.
CLEAN
The Clean
function is used to remove the non-printable characters from the given text, represented by numbers 0 to 31 of the 7-bit ASCII code.
Syntax:
CLEAN(Text)
where:
- Text: Required. String or text from which to remove non-printable characters.
CODE
The Code
function converts the first character of a supplied text string into a numeric character set code.
Syntax:
CODE(name)
where:
- name is the text for which you want the code of the first character.
CONCATENATE
Concatenate
joins several text strings into one text string.
Syntax:
CONCATENATE (text1, text2,…)
where:
- text1, text2 … are text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.
DOLLAR
The DOLLAR
function converts a number to text by using the CURRENCY format. The format used as $#,##0.00_);($#,##0.00).
Syntax:
DOLLAR(number, decimal_places)
where:
-
number is the number you want to convert to text.
-
decimal_places is the number of digits in decimal places you want to display. The value is rounded accordingly.
EXACT
The Exact
function compares two values ignoring the styles and returns the Boolean value as true or false.
Syntax:
EXACT(value1, value2)
where:
-
value1 is the first value you want to compare.
-
value2 is the second value you want to compare.
FIND
The Find
function finds one text string (text1) within another text string (text2) and returns the number of the starting position of text1, based on the number of bytes each character uses, from the first character of text2.
Syntax:
FIND(text1,text2, num)
where:
-
text1 is the text that is to be found.
-
text2 is the text that contains the found text.
-
num specifies the character where to start the search.
Remarks:
-
#VALUE!
-occurs when text1 does not appear in text2 and when num is not greater than zero.
FINDB
The FindB
function finds one text string (text1) within another text string (text2) and returns the number of the starting position of text1 based on the number of bytes each character uses from the first character of text2. At present, it finds the position of string based on single-byte character set.
Syntax:
FINDB(text1,text2, num)
where:
-
text1 is the text to be found.
-
text2 is the text that contains the found text.
-
num specifies a character, from where the search to be started.
Remarks:
-
#VALUE!
-occurs when text1 does not appear in text2 and when num is not greater than zero.
FIXED
The Fixed
function rounds off to a specified number of decimal places and returns the value in text format.
Syntax:
FIXED(number, decimal_places, no_commas)
where:
-
number is the number you want to round.
-
decimal_places is the number of decimal places you want to display in the result.
-
no_commas is a logical value. This displays commas when it is set to
false
and does not display commas when it is set totrue
.
LEFT
The Left
function returns the first character or characters in a text string, based on the number of bytes you specify.
Syntax:
LEFT(text, bytes)
where:
-
text is a string that contains the characters that you want to return.
-
bytes specify the number of characters
LEN
The Len
function returns the number
Syntax:
LEN(name)
where:
- name is the text whose length you want to find.
LOWER
The Lower
function converts all characters in the specified text string to lowercase. Characters in the string that are not letters are not changed.
Syntax:
LOWER(text)
where:
- text is the string you want to convert to lowercase.
MID
The Mid
function returns a specific number of characters from a text string, starting at the position you specify.
Syntax:
MID(text, startNum, numBytes)
where:
-
text is a string that contains the characters that you want to return.
-
startNum is the position of the first character that you want to extract in text.
-
numBytes specifies the number of characters you want in bytes.
NUMBERVALUE
The NumberValue
function converts text to a number in a locale-independent way.
Syntax:
NUMBERVALUE(text)
where:
- text is the text to be converted into a number.
Remarks:
-
#VALUE!
occurs when any of the argument is not valid.
PROPER
The Proper
function changes the first letter of text into a capital letter and following letters changes to lowercases.
Syntax:
PROPER(text)
where:
- text is the string which you want to capitalize the first letter.
REPLACE
The Replace
function replaces a certain part of text with a different part of text based on the number of characters given.
Syntax:
REPLACE(oldText, startNum, numChars, newText)
where:
-
oldText is the text that needs to be replaced.
-
startNum is the position of the character in oldText.
-
numChars is the number of characters needs to be replaced.
-
newText is the text that replaces the character in old text.
REPT
The Rept
function returns a supplied text string, repeated a specified number of times.
Syntax:
REPT(string, number)
where:
-
string is the text that you want to repeat.
-
num is the number of times to repeat the text.
Remarks:
- Blank text-occurs when number is zero.
RIGHT
The Right
function returns the last character or characters in a string, based on the number.
Syntax:
RIGHT(string, num)
where:
-
string contains the characters you want to return.
-
num specifies the number of characters.
SEARCH
The Search
function finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text.
Syntax:
SEARCH(findText,withinText, startNum )
where:
-
findText is the text that you want to find.
-
withinText is the text in which you want to search findText.
-
startNum is the character number in withinText, where you want to start the search.
Remark:
-
#VALUE!
occurs when find Text is not found.
SUBSTITUTE
It substitutes
new_text for old_text in a text string. Use Substitute when you want to replace specific text in a text string; use Replace when you want to replace any text that occurs in a specific location in a text string.
Syntax:
SUBSTITUTE(text, old_text, new_text, instance_num)
where:
-
Text is the text or the reference to a cell containing text in which you want to substitute characters.
-
Old_text is the text you want to replace.
-
New_text is the text you want to replace old_text with.
-
Instance_num specifies the occurrence of old_text you want to replace with the new_text. When you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
T
The T
function tests whether the given value is a text or not. When the given value is a text, then it returns the given text. Otherwise, the function returns as an empty text string.
Syntax:
T( value )
where:
-
value is a required value to be checked.
-
When the value is not a number or logical value, then the function returns as an empty string.
TEXT
The Text
function converts the supplied value into text by using the user specified format.
Syntax
TEXT(value, format_text)
where:
-
value is a numeric value to convert to text.
-
format_text is a format text to apply to the supplied value.
TRIM
The Trim
function returns a text value with the leading and trailing spaces removed.
Syntax:
TRIM( text )
where:
- text is the text value for which you want to remove the leading and the trailing spaces.
UNICHAR
The Unichar
function retrieves the Unicode character for a given numeric value.
Syntax:
UNICHAR(num)
where:
- num is the Unicode number that represents the character.
Remarks:
-
#N/A
-occurs when data types are not valid. -
#VALUE!
-occurs when num falls outside the allowable range, when number is zero.
UNICODE
The UNICODE
function calculates the number corresponding to the first character of the text.
Syntax:
UNICODE(text)
where:
- text is the character for which you want the Unicode value.
Remarks:
-
#VALUE!
-occurs when data type is not valid.
UPPER
The Upper
function converts all characters in a text string to uppercase.
Syntax:
UPPER(text )
where:
- text is the string you want to convert to uppercase.
VALUE
The Value
function computes the date or a string that contains the number, and converts it into number format.
Syntax:
VALUE(range)
where:
- range is the string that contains the date or a number.
TEXTJOIN
The TEXTJOIN
function joins the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. It allows you to supply a range of cells, and has a setting to ignore empty values.
Syntax:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
where:
-
delimiter (required): A separator between each text value in the resulting string. Most commonly, you would use a delimiter such as a comma or space character.
-
ignore_empty (required): If it is
TRUE
ignores the empty values, andFALSE
includes empty values in the result. -
text1 (required) :First text item to be joined.
-
[text2, …] (optional): Additional text items to be joined. There can be up to 252 strings that are joined together.
Remarks:
- The result string exceeds cell limit (32767 characters), it returns the
#VALUE!
error message.
CONCAT
The CONCAT
function joins the text from multiple ranges and/or strings, but it doesn’t provide delimiter or IgnoreEmpty arguments.
Syntax:
CONCAT(text1, [text2],…)
where:
-
text1 (required): It is a first text item to be joined. A string, or array of strings, such as a range of cells.
-
[text2, …] (optional): Additional text items to be joined. There can be upto 253 text arguments for the text items.
Remarks:
- If the result string exceeds cell limit (32767 characters), TEXTJOIN returns the
#VALUE!
error.
LEFTB
The LEFTB
function calculates the first character or characters in a text string, based on the number of bytes you specify.
Syntax:
LEFTB(text, bytes)
where:
-
text is a string that contains the characters that you want to return.
-
bytes specify the number of characters
RIGHTB
The RIGHTB
function returns the last character or characters in a text string, based on the number of bytes.
Syntax:
RIGHTB(string, num_bytes)
where:
-
string contains the characters you want to return.
-
num_bytes specifies the number of characters based on the number of bytes.
MIDB
The MIDB
function returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify.
Syntax:
MIDB(text, startNum, numBytes)
where:
-
text is a string that contains the characters that you want to return.
-
startNum is the position of the first character that you want to extract in text.
-
numBytes specifies the number of characters you want in bytes.
LENB
The LENB
function returns the number of bytes used to represent the characters in a text string.
Syntax:
LENB(name)
where:
- name is the text whose length you want to find.
REPLACEB
The REPLACEB
function replaces part of a text string, based on the number of bytes you specify, with a different text string.
Syntax:
REPLACEB(oldText, startNum, num_bytes, newText)
where:
-
oldText is the text that needs to be replaced.
-
startNum is the position of the character in oldText.
-
num_bytes is the number of bytes in old_text that you want REPLACEB to replace with new_text.
-
newText is the text that replaces the character in old text.
ASC
The ASC
function used to converts full-width (double-byte) characters to half-width (single-byte) characters.
Syntax:
ASC(text)
where:
- The text or a reference to convert to single byte characters.
JIS
The JIS
function converts single byte characters into double byte characters in a text string.
Syntax:
JIS(text)
where:
- The text or a reference that contains the text you want to change. If text does not contain any half-width English letters, text is not changed.
SEARCHB
The SEARCHB
function returns the position of a specified character or sub-string within a given text string.
Syntax:
SEARCHB(findText,withinText, startNum)_
where:
-
findText is the the character or sub-string that you want to find.
-
withinText is the text string that in which you want to search findText.
-
startNum is specifies the position in the within_text string, from which the search should begin.
Remark:
- The find text is not found it return
#VALUE!
error message.
UNIQUE
The UNIQUE
function returns a list of unique values from a specified range or array.
Syntax:
UNIQUE(array, [by_col], [exactly_once])
where:
-
array: The range or array from which to return unique values.
-
[by_col] (Optional): If
TRUE
, compares columns for uniqueness; ifFALSE
or omitted, compares rows. -
[exactly_once] (Optional): If
TRUE
, returns only values that appear exactly once; ifFALSE
or omitted, returns all distinct values.
Remarks:
-
The result will spill into adjacent cells if it’s the final result of a formula.
-
If [exactly_once] is set to
TRUE
, values that appear more than once are excluded from the result. -
If [by_col] is
TRUE
, the function evaluates unique columns instead of rows.
TEXTAFTER
The TEXTAFTER
function extracts the text that appears after a specified delimiter in a string.
Syntax:
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
where:
-
text: The input text to search within.
-
delimiter: The text or character after which you want to extract.
-
[instance_num] (Optional): The instance of the delimiter to search for. Defaults to 1 (first instance).
-
[match_mode] (Optional): Determines if the search is case-sensitive (0 for case-sensitive, 1 for case-insensitive).
-
[match_end] (Optional): If
TRUE
, treats the end of the text as a delimiter. -
[if_not_found] (Optional): Value to return if the delimiter is not found. Defaults to
#N/A
.
Remarks:
-
If the delimiter is not found, a
#N/A
error is returned, unless [if_not_found] is specified. -
If [instance_num] equal to 0 or greater than the length of text, a
#VALUE!
error returned. -
The [instance_num] argument allows for both positive and negative numbers. Positive values search from the start, and negative values search from the end.
-
The function supports case-sensitive and case-insensitive search modes using the [match_mode] argument.
«««< HEAD
3 Dec 202424 minutes to read
664175cf483e78a9bac155018dbdc92b8216b891
TEXTBEFORE
The TEXTBEFORE
function extracts the text that appears before a specified delimiter in a string.
Syntax:
TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
where:
-
text: The input text to search within.
-
delimiter: The text or character before which you want to extract.
-
[instance_num] (Optional): The instance of the delimiter to search for. Defaults to 1 (first instance).
-
[match_mode] (Optional): Case-sensitivity control (0 for case-sensitive, 1 for case-insensitive).
-
[match_end] (Optional): If TRUE, treats the end of the text as a delimiter.
-
[if_not_found] (Optional): Value to return if the delimiter is not found. Defaults to #N/A.
Remarks:
-
If the delimiter is not found, a #N/A error is returned, unless if_not_found is specified.
-
if [instance_num] is equal to 0 or greater than the length of text, a
#VALUE!
error returned. -
Similar to
TEXTAFTER
, negative [instance_num] values search from the end of the string. -
The function supports both case-sensitive and case-insensitive search modes using the [match_mode] argument.
VALUETOTEXT
The VALUETOTEXT
function converts a value into a text format.
Syntax:
VALUETOTEXT(value, [format])
where:
-
value: The value to return to text.
-
[format] (Optional): Determines the format:
-
0: Concise format (default).
-
1: Strict format with escape characters for formulas.
-
«««< HEAD
=======
664175cf483e78a9bac155018dbdc92b8216b891
Remarks:
-
If an invalid format is provided,
#VALUE!
error is returned. -
The concise format is human-readable, while the strict format preserves data structure.
ARRAYTOTEXT
The ARRAYTOTEXT
function function converts an array into text.
«««< HEAD
=======
664175cf483e78a9bac155018dbdc92b8216b891
Syntax:
ARRAYTOTEXT(array, [format])
where:
-
array: The array to convert to text.
-
[format] (Optional): Determines the output format:
-
0: Concise format (default).
-
1: Strict format with escape characters and row delimiters.
-
Remarks:
-
The concise format displays the output within a single cell, while the strict format presents a more detailed array layout with escape characters.
-
If the
format
argument is not 0 or 1, a#VALUE!
error is returned.
TOCOL
The TOCOL
function transforms an array into a single column.
Syntax:
TOCOL(array, [ignore], [scan_by_column])
where:
-
array: The array to convert into a column.
-
[ignore] (Optional): Specifies which values to ignore:
-
0: Keep all values (default).
-
1: Ignore blanks.
-
2: Ignore errors.
-
3: Ignore blanks and errors.
-
-
[scan_by_column] (Optional): If
TRUE
, scans by column instead of by row.
Remarks:
-
The function will return all values in a single column, even if the original array was multidimensional.
-
If [scan_by_column] is set to
TRUE
, the function will scan the array by column; otherwise, it defaults to scanning by row. -
If an array contains non-integer values, the function returns a
#VALUE!
error.
TOROW
The TOROW
function transforms an array into a single row.
Syntax:
TOROW(array, [ignore], [scan_by_column])
where:
-
array: The array to convert into a row.
-
[ignore] (Optional): Specifies which values to ignore:
-
0: Keep all values (default)
-
1: Ignore blanks.
-
2: Ignore errors.
-
3: Ignore blanks and errors.
-
-
[scan_by_column] (Optional): If
TRUE
, scans by column.
Remarks:
-
The function will return all values in a single row, even if the original array was multidimensional.
-
If [scan_by_column] is set to
TRUE
, the function will scan the array by column; otherwise, it defaults to scanning by row. -
If an array contains non-integer values, the function returns a
#VALUE!
error.
CHOOSECOLS
The CHOOSECOLS
function returns specified columns from an array.
Syntax:
CHOOSECOLS(array, col_num1, [col_num2], …)
where:
-
array: The array containing columns to be selected.
-
col_num1: The index of the first column to return.
-
[col_num2] (Optional): Additional columns to return.
Remarks:
«««< HEAD
=======
664175cf483e78a9bac155018dbdc92b8216b891
- If any [col_num] is zero or exceeds the total number of columns, a
#VALUE!
error is returned.
- The function is useful for extracting only certain columns from a large dataset.
«««< HEAD
CHOOSEROWS
The CHOOSEROWS
function returns specified rows from an array.
Syntax:
CHOOSEROWS(array, row_num1, [row_num2], …)
where:
-
array: The array containing the rows to be selected.
-
row_num1: The index of the first row to return.
-
[row_num2] (Optional): Additional rows to return.
Remarks:
=======
CHOOSEROWS
The CHOOSEROWS
function returns specified rows from an array.
Syntax:
CHOOSEROWS(array, row_num1, [row_num2], …)
where:
-
array: The array containing the rows to be selected.
-
row_num1: The index of the first row to return.
-
[row_num2] (Optional): Additional rows to return.
Remarks:
664175cf483e78a9bac155018dbdc92b8216b891
- A
#VALUE!
error occurs if any [row_num] exceeds the number of rows or is zero.
- The function is useful for extracting specific rows from a dataset.
SEQUENCE
The SEQUENCE
function generates a sequence of numbers in an array.
Syntax:
SEQUENCE(rows, [columns], [start], [step])
where:
-
rows: The number of rows for the sequence.
-
[columns] (Optional): The number of columns.
-
[start] (Optional): The starting number.
-
[step] (Optional): The increment for each number.
Remarks:
-
Defaults to 1 for all omitted optional arguments. If no start or step values are specified, the sequence starts at 1 and increments by 1.
-
The function spills the result into adjacent cells when it generates the array.
-
SEQUENCE
can create multi-dimensional arrays by specifying both rows and columns.
TEXTSPLIT
The TEXTSPLIT
function splits text into arrays based on delimiters.
Syntax:
TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
where:
-
text: The input text to split.
-
col_delimiter: The delimiter to split the text into columns.
-
[row_delimiter] (Optional): The delimiter to split the text into rows.
-
[ignore_empty] (Optional): If
TRUE
, ignores consecutive delimiters. Defaults toFALSE
. -
[match_mode] (Optional): If 1, performs a case-insensitive match. Defaults to 0 (case-sensitive).
-
[pad_with] (Optional): The value to use for padding missing elements. Defaults to
#N/A
.
Remarks:
-
If no [row_delimiter] is provided, the function will only split by columns. You can use multiple delimiters by specifying an array of delimiters.
-
The [ignore_empty] option allows consecutive delimiters to be ignored, which is useful when parsing text that contains multiple delimiters.
-
The [pad_with] argument is used to fill in empty cells when the result array is not rectangular.
TAKE
The TAKE
function returns a specified number of contiguous rows or columns from the start or end of an array.
Syntax:
TAKE(array, rows, [columns])
where:
-
array: The array from which to take rows or columns.
-
rows: The number of rows to take. A negative value takes rows from the end of the array.
-
[columns] (Optional): The number of columns to take. A negative value takes columns from the end of the array.
Remarks:
-
TAKE
returns a#CALC!
error if either rows or columns are set to 0, as this results in an empty array. -
If the specified array is too large,
TAKE
returns a#NUM!
error. -
The function allows easy extraction of portions of data, especially for summarizing or splitting tables.
DROP
The DROP
function excludes a specified number of rows or columns from the start or end of an array. This function is useful for removing headers, footers, or sections of data in an array.
Syntax:
DROP(array, rows, [columns])
where:
-
array: The array from which to drop rows or columns.
-
rows: The number of rows to drop. A negative value drops from the end of the array.
-
[columns] (Optional): The number of columns to drop. A negative value drops columns from the end.
Remarks:
-
A
#CALC!
error is returned if rows or columns are set to 0, creating an empty array. -
If the array exceeds the allowed size limit,
DROP
returns a#NUM!
error. -
The function helps to clean up datasets, such as removing unnecessary columns or rows before analysis.
«««< HEAD
=======
664175cf483e78a9bac155018dbdc92b8216b891
EXPAND
The EXPAND
function expands or pads an array to specified row and column.
Syntax:
EXPAND(array, rows, [columns], [pad_with])
where:
-
array: The array to expand.
-
rows: The number of rows in the expanded array.
-
[columns] (Optional): The number of columns in the expanded array.
-
[pad_with] (Optional): The value to pad with if the array is smaller than the target size. Defaults to
#N/A
.
Remarks:
-
If the rows or columns argument is less than the original array’s dimensions,
EXPAND
will return a#VALUE!
error. -
Padded cells use the [pad_with] argument; if omitted, it defaults to
#N/A
. -
Useful for filling or aligning datasets to match specific table structures.
WRAPROWS
The WRAPROWS
function wraps a one-dimensional vector into rows after a specified number of elements to form a new array.
Syntax:
WRAPROWS(vector, wrap_count, [pad_with])
where:
-
vector: The row or column of values to wrap.
-
wrap_count: The maximum number of elements in each row.
-
[pad_with] (Optional): The value used to fill in any remaining cells if the array cannot be evenly divided. Defaults to
#N/A
.
Remarks:
-
If the [wrap_count] is greater than the length of the vector, the vector is returned in a single row.
-
The function returns a
#VALUE!
error if the input is not a one-dimensional array. -
If [wrap_count] is less than 1, a
#NUM!
error occurs. -
Cells that cannot be filled are assigned
#N/A
, unless otherwise specified with [pad_with].
«««< HEAD
=======
664175cf483e78a9bac155018dbdc92b8216b891
WRAPCOLS
The WRAPCOLS
function wraps a one-dimensional vector into columns after a specified number of elements to form a new array.
Syntax:
WRAPCOLS(vector, wrap_count, [pad_with])
where:
-
vector: The row or column of values to wrap.
-
wrap_count: The maximum number of elements in each column.
-
[pad_with] (Optional): The value used to fill in any remaining cells if the array cannot be evenly divided. Defaults to
#N/A
.
Remarks:
- If [wrap_count] exceeds the length of the vector, the vector is returned in a single column.
«««< HEAD
- If the vector is not a one-dimensional array, a
#VALUE!
error is returned.
=======
- If the vector is not a one-dimensional array, a
#VALUE!
error is returned.
664175cf483e78a9bac155018dbdc92b8216b891
- A
#NUM!
error occurs if [wrap_count] is less than 1.
- Empty cells in the new array are filled with
#N/A
unless specified otherwise.
SORT
The SORT
function sorts the contents of an array in either ascending or descending order.
«««< HEAD
=======
664175cf483e78a9bac155018dbdc92b8216b891
Syntax:
SORT(array, [sort_index], [sort_order], [by_col])
where:
-
array: The array or range to sort.
-
[sort_index] (Optional): The index (row or column number) to sort by. Defaults to the first row/column.
-
[sort_order] (Optional): 1 for ascending (default), -1 for descending.
-
[by_col] (Optional):
FALSE
(default) to sort by rows, orTRUE
to sort by columns.
Remarks:
-
When [sort_index] or [sort_order] is omitted, Excel defaults to sorting the first row or column in ascending order.
-
SORT
automatically spills the result into adjacent cells, making it convenient for dynamic arrays. -
Use the
SORTBY
function when you need more flexible sorting based on multiple criteria.
SORTBY
The SORTBY
function sorts an array based on values in a separate corresponding array or range.
Syntax:
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)
where:
-
array: The array to sort.
-
by_array1: The array or range to sort by.
-
[sort_order1] (Optional): 1 for ascending (default), -1 for descending.
-
[by_array2], [sort_order2] (Optional): Additional arrays and their sort orders, if needed.
Remarks:
-
The function allows sorting on multiple criteria.
-
Results are returned in a dynamic array that spills into adjacent cells.
-
If no sort order is specified, Excel defaults to ascending order.
-
Unlike
SORT
,SORTBY
enables sorting based on multiple, unrelated criteria or arrays.
FILTER
The FILTER
function returns an array of values filtered based on criteria you define.
Syntax:
FILTER(array, include, [if_empty])
where:
-
array: The array, or range to filter.
-
include: A Boolean array (same size as array) that indicates which values to keep.
-
[if_empty] (Optional): The value to return if no results match the criteria. Defaults to a
#CALC!
error.
Remarks:
-
The
FILTER
function automatically spills the results into adjacent cells. -
If no matches are found, and [if_empty] is not defined, Excel returns a
#CALC!
error. -
FILTER
will return an error if the include array is not Boolean, or if it contains errors such as#N/A
or#VALUE!
.
BAHTTEXT
The function BAHTTEXT
converts a number to Thai text and adds the suffix “Baht.”
Syntax
BAHTTEXT(number)
Where:
-
number: A number you want to convert to text. This can be:
-
A numeric value
-
A reference to a cell containing a number
-
A formula that evaluates to a number
-
Remarks:
- The output of
BAHTTEXT
is in Thai text with “Baht” as the suffix.