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 to true.

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.

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.

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, and FALSE 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; if FALSE or omitted, compares rows.

  • [exactly_once] (Optional): If TRUE, returns only values that appear exactly once; if FALSE 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 to FALSE.

  • [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, or TRUE 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.