text functions
join, split, clean, search and format strings.
| function | signature | what it does | tests |
|---|---|---|---|
| ASC | ASC(text) | converts full-width characters to half-width | — |
| CHAR | CHAR(number) | returns the character for a character-set code | 1 |
| CLEAN | CLEAN(text) | removes non-printable characters | — |
| CODE | CODE(text) | numeric code of the first character | — |
| CONCAT | CONCAT(text…) | joins multiple text strings into one | — |
| CONCATENATE | CONCATENATE(text…) | joins several text strings into one | 1 |
| CONTAINS | CONTAINS(text, substring) | checks if text contains substring | 3 |
| DOLLAR | DOLLAR(number, [decimals]) | formats a number as currency text | — |
| ENDSWITH | ENDSWITH(text, suffix) | checks if text ends with suffix | 1 |
| EXACT | EXACT(text1, text2) | checks if two text strings are identical | 6 |
| FIND | FIND(find_text, within_text, [start_num]) | finds text within another text, case-sensitive | 1 |
| FINDB | FINDB(find_text, within_text, [start_num]) | finds text within text by bytes, case-sensitive | — |
| FIXED | FIXED(number, [decimals], [no_commas]) | formats a number as text with a fixed number of decimals | — |
| JOIN | JOIN(delimiter, value_or_array…) | joins values with delimiter | 6 |
| LEFT | LEFT(text, [num_chars]) | returns leftmost characters from text | 1 |
| LEFTB | LEFTB(text, [num_bytes]) | returns leftmost bytes from text | — |
| LEN | LEN(text) | returns number of characters in text | — |
| LENB | LENB(text) | returns number of bytes in text | — |
| LOWER | LOWER(text) | converts text to lowercase | 3 |
| MID | MID(text, start_num, num_chars) | returns characters from middle of text | — |
| MIDB | MIDB(text, start_num, num_bytes) | returns bytes from middle of text | — |
| NUMBERVALUE | NUMBERVALUE(text, [decimal_separator], [group_separator]) | converts text to a number, locale-independent | — |
| PROPER | PROPER(text) | capitalizes the first letter of each word | — |
| REGEXEXTRACT | REGEXEXTRACT(text, pattern, [return_mode]) | extracts the first regex match | — |
| REGEXREPLACE | REGEXREPLACE(text, pattern, replacement) | replaces regex matches with new text | — |
| REGEXTEST | REGEXTEST(text, pattern) | whether text matches a regular expression | — |
| REPLACE | REPLACE(old_text, start_num, num_chars, new_text) | replaces characters by position | — |
| REPLACEB | REPLACEB(old_text, start_num, num_bytes, new_text) | replaces characters by byte position | — |
| REPT | REPT(text, number_times) | repeats text a given number of times | — |
| RIGHT | RIGHT(text, [num_chars]) | returns rightmost characters from text | 2 |
| RIGHTB | RIGHTB(text, [num_bytes]) | returns rightmost bytes from text | — |
| SEARCH | SEARCH(find_text, within_text, [start_num]) | finds text within another text, case-insensitive | — |
| SEARCHB | SEARCHB(find_text, within_text, [start_num]) | finds text within text by bytes, case-insensitive | — |
| SPLIT | SPLIT(text, delimiter, [split_by_each], [remove_empty_text]) | splits text by delimiter into multiple cells | 7 |
| STARTSWITH | STARTSWITH(text, prefix) | checks if text starts with prefix | 1 |
| SUBSTITUTE | SUBSTITUTE(text, old_text, new_text, [instance_num]) | replaces old text with new text | — |
| T | T(value) | returns text if value is text, else empty | — |
| TEXT | TEXT(value, format_text) | formats number as text with given format | 16 |
| TEXTAFTER | TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | returns text after a delimiter | — |
| TEXTBEFORE | TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) | returns text before a delimiter | — |
| TEXTCAPITALIZE | TEXTCAPITALIZE(text) | capitalizes first letter of text | 1 |
| TEXTCENTER | TEXTCENTER(text, length, [pad_char]) | centers text with padding to reach specified length | 1 |
| TEXTCOMPARE | TEXTCOMPARE(text1, text2, [case_sensitive]) | compares two texts and returns -1, 0, or 1 | 1 |
| TEXTCOUNT | TEXTCOUNT(text, substring, [case_sensitive]) | counts occurrences of substring in text | 2 |
| TEXTINDENT | TEXTINDENT(text, indent, [indent_char]) | indents text with specified number of characters | 1 |
| TEXTJOIN | TEXTJOIN(delimiter, ignore_empty, text…) | joins text with delimiter, ignoring blanks | — |
| TEXTPADLEFT | TEXTPADLEFT(text, length, [pad_char]) | pads text on the left with character to reach specified length | 2 |
| TEXTPADRIGHT | TEXTPADRIGHT(text, length, [pad_char]) | pads text on the right with character to reach specified length | 1 |
| TEXTREVERSE | TEXTREVERSE(text) | reverses text | 2 |
| TEXTSPLIT | TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) | splits text by delimiters into an array | — |
| TEXTSWAPCASE | TEXTSWAPCASE(text) | swaps uppercase and lowercase letters | 1 |
| TEXTTITLE | TEXTTITLE(text) | converts text to title case | 1 |
| TEXTWRAP | TEXTWRAP(text, width) | wraps text at specified width | 3 |
| TRIM | TRIM(text) | removes extra spaces from text | — |
| UNICHAR | UNICHAR(number) | returns the character for a unicode code point | — |
| UNICODE | UNICODE(text) | returns unicode number of first character | 2 |
| UPPER | UPPER(text) | converts text to uppercase | 1 |
| VALUE | VALUE(text) | converts text string to a number | 17 |
| VALUETOTEXT | VALUETOTEXT(value, [format]) | returns text from any value type | — |