Text functions
The 48 text functions in Sheets, with the usual names, arguments and results.
| Function | What it does | Syntax |
|---|---|---|
| ARRAYTOTEXT | Values of an array as one text | ARRAYTOTEXT(array, [format]) |
| ASC | Full-width characters to half-width | ASC(text) |
| BAHTTEXT | Number as Thai baht text | BAHTTEXT(number) |
| CHAR | Character for a code from 1 to 255 | CHAR(number) |
| CLEAN | Remove nonprinting characters | CLEAN(text) |
| CODE | Code of the first character | CODE(text) |
| CONCATexample | Join text strings | CONCAT(text1, text2, ...) |
| CONCATENATE | Join text strings | CONCATENATE(text1, text2, ...) |
| DBCS | Half-width characters to full-width | DBCS(text) |
| DOLLAR | Number as currency text | DOLLAR(number, [decimals]) |
| ENCODEURL | URL-encoded text | ENCODEURL(text) |
| EXACT | Whether two texts are identical | EXACT(text1, text2) |
| FIND | Position of text (case-sensitive) | FIND(find_text, within_text, [start]) |
| FINDB | Position of text (case-sensitive) | FINDB(find_text, within_text, [start_num]) |
| FIXED | Number as text with fixed decimals | FIXED(number, [decimals], [no_commas]) |
| JIS | Half-width characters to full-width | JIS(text) |
| LEFTexample | First characters of text | LEFT(text, [count]) |
| LEFTB | First characters of text | LEFTB(text, [num_bytes]) |
| LENexample | String length | LEN(text) |
| LENB | Length of text | LENB(text) |
| LOWERexample | Lowercase | LOWER(text) |
| MID | Characters from the middle of text | MID(text, start, count) |
| MIDB | Characters from the middle of text | MIDB(text, start_num, num_bytes) |
| NUMBERVALUE | Text to number with given separators | NUMBERVALUE(text, [decimal_separator], [group_separator]) |
| PHONETIC | Phonetic (furigana) text of a cell | PHONETIC(reference) |
| PROPERexample | Capitalize each word | PROPER(text) |
| REGEXEXTRACT | Text that matches a regular expression | REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) |
| REGEXREPLACE | Replace regular expression matches | REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) |
| REGEXTEST | Whether text matches a regular expression | REGEXTEST(text, pattern, [case_sensitivity]) |
| REPLACE | Replace characters at a position | REPLACE(old_text, start_num, num_chars, new_text) |
| REPLACEB | Replace characters at a position | REPLACEB(old_text, start_num, num_bytes, new_text) |
| REPT | Repeat text | REPT(text, times) |
| RIGHT | Last characters of text | RIGHT(text, [count]) |
| RIGHTB | Last characters of text | RIGHTB(text, [num_bytes]) |
| SEARCH | Position of text (any case, wildcards) | SEARCH(find_text, within_text, [start]) |
| SEARCHB | Position of text, wildcards allowed | SEARCHB(find_text, within_text, [start_num]) |
| SUBSTITUTEexample | Replace text | SUBSTITUTE(text, old_text, new_text, [instance]) |
| TEXTexample | Format a number or date as text | TEXT(value, format) |
| TEXTAFTER | Text after a delimiter | TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) |
| TEXTBEFORE | Text before a delimiter | TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]) |
| TEXTJOINexample | Join texts with a delimiter | TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) |
| TEXTSPLIT | Split text into columns and rows | TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) |
| TRIMexample | Remove extra spaces | TRIM(text) |
| UNICHAR | Character for a Unicode number | UNICHAR(number) |
| UNICODE | Unicode number of the first character | UNICODE(text) |
| UPPER | Uppercase | UPPER(text) |
| VALUE | Convert text to a number | VALUE(text) |
| VALUETOTEXT | Any value as text | VALUETOTEXT(value, [format]) |