What is an TEXT formula in a spreadsheet?
Text formulas and functions are designed for manipulating and formatting text strings. They allow you to concatenate text, extract substrings, convert text cases, replace characters, perform string manipulations, and format text according to specific requirements.TEXT formula usage examples.
The ARABIC function is used to convert a Roman numeral to an Arabic number. It takes a Roman numeral as input and returns the corresponding Arabic number. This function can be useful when working with historical data or when performing calculations involving Roman numerals.
The ASC function is used to convert full-width ASCII and katakana characters to their half-width counterparts. All standard-width characters will remain unchanged.
The CHAR function is used to convert a number into a character according to the current Unicode table. It takes a single argument, the table number, and returns the corresponding character. The table number must be an integer between 1 and 255.
The CLEAN function is used to remove non-printable ASCII characters from a given text. Non-printable characters include characters with ASCII codes 0-31 and 127. These characters are typically not visible and can cause issues when working with text data. The CLEAN function replaces these characters with a space character, except for line breaks, which are preserved. The function takes a single argument, the text string from which to remove the non-printable characters.
The CODE function returns the numeric Unicode map value of the first character in the provided string. It is useful for working with international characters and encoding schemes.
The CONCATENATE function is used to combine multiple strings into a single string. It takes one or more string arguments and returns the concatenated result. The function does not modify the original strings and can handle empty strings as arguments.
The DOLLAR function formats a number into the locale-specific currency format. It takes two arguments: the number to be formatted and the number of decimal places to display (optional). The function returns the formatted number as a text string.
The EXACT function is used to test whether two strings are identical. It returns TRUE if the strings are exactly the same, including case, and FALSE otherwise.
The FIND function is used to determine the position at which a specified substring is first found within a given text. It returns the numeric position of the first character of the substring in the text. If the substring is not found, it returns the #VALUE! error.
The FINDB function is used to find the position at which a string is first found within text, counting each double-character as 2. It returns the position as a number. The optional 'starting_at' argument specifies the character position at which the search should start. If not provided, the search starts from the beginning of the text.
The FIXED function is used to format a number with a fixed number of decimal places. It takes three arguments: 'number' is the number to be formatted, 'number_of_places' is the desired number of decimal places, and 'suppress_separator' is an optional argument to suppress the thousands separator. The function returns the formatted number as text.
The JOIN function concatenates the elements of one or more one-dimensional arrays using a specified delimiter. It returns a string that consists of the elements joined together with the delimiter in between.
The LEFT function returns a substring from the beginning of a specified string. It takes two arguments: the string from which to extract the substring, and the number of characters to extract. If the number of characters is not specified, it defaults to 1. The function is commonly used to extract the leftmost characters of a string or to split a string based on a delimiter.
The LEFTB function returns the left portion of a string up to a certain number of bytes. It is similar to the LEFT function, but it considers the number of bytes instead of characters. This is useful when working with multibyte character sets, such as Chinese or Japanese.
The LEN function returns the number of characters in a text string. It counts all characters, including spaces and punctuation marks.
The LENB function is used to calculate the length of a string in bytes. It counts each character as 2 bytes, regardless of the character's actual byte length. This function is useful when working with multibyte character sets, such as double-byte character sets (DBCS) or Unicode.
The LOWER function is used to convert a specified string to lowercase. It takes a single argument, which is the text to be converted. The function returns the lowercase version of the text.
The MID function is used to extract a segment of a string based on the starting position and the length of the segment. It returns the specified segment of the string.
The MIDB function is used to return a section of a string starting at a given character and up to a specified number of bytes. It is similar to the MID function, but it considers double-byte characters as 2 bytes.
The PROPER function capitalizes the first letter of each word in a specified string. It converts all other letters to lowercase. This function is useful for formatting names, titles, and cleaning up data.
The REGEXEXTRACT function is used to extract matching substrings from a text string based on a specified regular expression pattern. It takes two arguments: the text string to search in and the regular expression pattern to match. The function returns the first substring that matches the pattern. If no match is found, it returns an empty string. The regular expression pattern can be used to define complex matching patterns, allowing for flexible and powerful text extraction.
The REGEXMATCH function is used to determine whether a piece of text matches a regular expression. It returns TRUE if the text matches the regular expression, and FALSE otherwise. The regular expression can be a simple pattern or a more complex expression. This function is useful for tasks such as validating input data, searching for specific patterns in text, or filtering data based on a pattern match.
The REGEXREPLACE function is used to replace part of a text string with a different text string using regular expressions. It takes three arguments: the original text, the regular expression pattern to match, and the replacement text. The function searches for all occurrences of the regular expression pattern in the original text and replaces them with the replacement text.
The REPLACE function is used to replace part of a text string with a different text string. It takes four arguments: 'text' is the original text string, 'position' is the starting position of the text to be replaced, 'length' is the number of characters to be replaced, and 'new_text' is the text string to replace the specified part with.
The REPLACEB function is used to replace part of a text string, based on a number of bytes, with a different text string. It takes four arguments: 'text' is the original text string, 'position' is the starting position of the part to be replaced, 'num_bytes' is the number of bytes to be replaced, and 'new_text' is the text string to replace the specified part.
The REPT function is used to repeat a specific text or character a certain number of times. It takes two arguments: 'text_to_repeat' which is the text or character to be repeated, and 'number_of_repetitions' which specifies how many times the text or character should be repeated.
The RIGHT function returns a substring from the end of a specified string. It takes two arguments: the string from which to extract the substring, and the number of characters to extract. If the number of characters is not specified, it defaults to 1. The function starts from the rightmost character of the string and extracts the specified number of characters.
The RIGHTB function returns the right portion of a string up to a certain number of bytes. It counts each character as 2 bytes if the character is a double-byte character, and 1 byte if it is a single-byte character.
The ROMAN function is used to format a number in Roman numerals. It takes a number as input and returns the corresponding Roman numeral representation. The function can also accept an optional rule_relaxation parameter to customize the conversion rules.
The SEARCH function is used to find the position at which a specified substring is first found within a given text. It returns the starting position of the substring within the text. The function is case-sensitive by default, but can be made case-insensitive by specifying a non-zero value for the optional 'starting_at' parameter.
The SEARCHB function is used to find the position at which a string is first found within text, counting each double-character as 2. It returns the position as a number.
The SPLIT function divides text around a specified character or string, and puts each fragment into a separate cell in the row. It takes the following parameters: - text: The text to be split. - delimiter: The character or string that specifies where to split the text. - split_by_each (optional): A logical value that determines whether to split the text by each occurrence of the delimiter. If set to TRUE, the text will be split by each occurrence of the delimiter. If set to FALSE or omitted, the text will be split by the first occurrence of the delimiter. - remove_empty_text (optional): A logical value that determines whether to remove empty text fragments from the result. If set to TRUE, empty text fragments will be removed. If set to FALSE or omitted, empty text fragments will be included in the result.
The SUBSTITUTE function is used to replace existing text with new text in a string. It takes four arguments: 'text_to_search' is the original string, 'search_for' is the text to be replaced, 'replace_with' is the new text to replace with, and 'occurrence_number' (optional) specifies which occurrence of 'search_for' to replace. If 'occurrence_number' is not provided, all occurrences of 'search_for' will be replaced.
The T function is used to return string arguments as text. It takes a single argument and returns the argument as text. If the argument is already text, it is returned as is. If the argument is a number, it is converted to text. If the argument is a boolean value, 'TRUE' or 'FALSE' is returned as text. If the argument is an error value, the error value is returned as text. If the argument is empty, an empty string is returned.
The TEXT function is used to convert a number into text according to a specified format. It allows you to customize the appearance of numbers, dates, and other values in Excel. The function takes two arguments: 'number' which is the value you want to convert, and 'format' which specifies the format you want to apply to the value. The 'format' argument uses special codes to represent different elements such as currency symbols, decimal places, and date formats.
The TEXTJOIN function combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts. It allows you to ignore empty cells and specify the order of the texts to be joined.
The TRIM function is used to remove leading and trailing spaces in a specified string. It does not affect spaces between words within the string. This function is commonly used to clean up data imported from external sources or to remove unnecessary spaces in text documents.
The UNICHAR function is used to return the Unicode character for a given number. It takes a single argument, 'number', which represents the Unicode value of the character. The function then returns the corresponding character based on the Unicode value provided.
The UNICODE function returns the decimal Unicode value of the first character of the text. It can be used to obtain the Unicode value of any character in a text string.
The UPPER function is used to convert a specified string to uppercase. It takes a single argument, 'text', which is the string to be converted. The function returns the uppercase version of the input string.