Text functions
Text functions split, join, clean and reshape words and codes. Use them to tidy imported data, build labels and pull pieces out of longer strings.
48 functions in this category.
Start here
- CONCAT
Joins text from several cells or values into one, and unlike CONCATENATE it accepts whole ranges. There is no delimiter option, so use TEXTJOIN when you need separators.
- TEXT
Converts a number or date into text in the layout you choose. Essential when combining numbers or dates with words in a sentence.
- VALUE
Converts number-like text into a real number so it can be used in math. Often fixes numbers imported as text.
- SEARCH
Finds where text appears inside other text, ignoring case and allowing wildcards. The forgiving cousin of FIND.
- TRIM
Removes leading and trailing spaces and collapses repeated spaces between words to one. First step in cleaning names and imported data.
- LEFT
Pulls a set number of characters from the start of a string, such as a prefix or area code. Combine with FIND to cut at a delimiter.
All text functions
| Function | What it does | Level | Since | Spills? |
|---|---|---|---|---|
ARRAYTOTEXT | Flattens a range or array into one piece of text. Handy for showing what a spilled result contains, or for pasting a list into a single cell. | Intermediate | 2021 | No |
ASC | Converts double-byte (full-width) characters to single-byte (half-width). It only does anything in East Asian language setups, such as Japanese; elsewhere it returns the text unchanged. | Expert | 2007 or earlier | No |
BAHTTEXT | Spells a number out in Thai words with the baht currency wording. It is niche, used mainly for Thai invoices and cheques. | Expert | 2007 or earlier | No |
CHAR | Returns the character for a numeric code in your system's character set. Mostly used to insert line breaks (CHAR(10)) inside a formula. | Intermediate | 2007 or earlier | No |
CLEAN | Strips invisible control characters, such as stray line feeds and tabs, that come along when you paste data from other systems. Often paired with TRIM when cleaning imports. | Intermediate | 2007 or earlier | No |
CODE | Gives the number behind the first character of a text string. Useful for diagnosing odd characters that will not match or trim away. | Intermediate | 2007 or earlier | No |
CONCAT | Joins text from several cells or values into one, and unlike CONCATENATE it accepts whole ranges. There is no delimiter option, so use TEXTJOIN when you need separators. | Beginner | 2019 | No |
DBCS | Converts single-byte characters to double-byte (full-width) ones. It only has an effect when an East Asian language such as Japanese is enabled; otherwise the text comes back unchanged. | Expert | 2007 or earlier | No |
DETECTLANGUAGE | Guesses which language a piece of text is written in. Useful for sorting multilingual survey comments or support tickets before translating them. | Advanced | 365 only | No |
DOLLAR | Turns a number into currency-formatted text using your system's currency symbol. The result is text, so it cannot be summed; format cells instead if you still need math. | Intermediate | 2007 or earlier | No |
EXACT | Compares two strings and cares about upper and lower case, unlike the = operator. Good for validating codes or case-sensitive IDs. | Intermediate | 2007 or earlier | No |
FIND | Returns where one piece of text first appears inside another. It is case-sensitive and does not allow wildcards, so use SEARCH if you need either flexibility. | Beginner | 2007 or earlier | No |
FINDB | Like FIND, but counts each double-byte character as two. It only differs from FIND when an East Asian language is enabled; otherwise it behaves identically. | Expert | 2007 or earlier | No |
FIXED | Rounds a number and returns it as text with thousands separators. Like DOLLAR but without a currency symbol. | Intermediate | 2007 or earlier | No |
LEFT | Pulls a set number of characters from the start of a string, such as a prefix or area code. Combine with FIND to cut at a delimiter. | Beginner | 2007 or earlier | No |
LEFTB | Like LEFT, but counts bytes, so each double-byte character uses two. It differs from LEFT only when an East Asian language is enabled. | Expert | 2007 or earlier | No |
LEN | Counts the characters in a string. Common for checking field lengths, spotting hidden spaces, or counting a character with SUBSTITUTE. | Beginner | 2007 or earlier | No |
LENB | Like LEN but counts double-byte characters as two. It only differs from LEN when an East Asian language is enabled. | Expert | 2007 or earlier | No |
LOWER | Converts all letters to lowercase. Useful for normalising emails or keys before matching. | Beginner | 2007 or earlier | No |
MID | Extracts characters from the middle of a string, given a start position and length. Pair with FIND or SEARCH to pull out text between delimiters. | Beginner | 2007 or earlier | No |
MIDB | Like MID but works in bytes, counting each double-byte character as two. It differs from MID only in East Asian language settings. | Expert | 2007 or earlier | No |
NUMBERVALUE | Converts text to a number while letting you say which characters are the decimal and thousands separators. This makes it reliable for data from other regions, unlike VALUE. | Intermediate | 2013 | No |
PHONETIC | Returns the phonetic reading attached to Japanese text. In other language setups it simply returns the text itself. | Expert | 2007 or earlier | No |
PROPER | Capitalises the first letter of every word and lowercases the rest. Good for tidying names, but names like McDonald need a manual fix. | Intermediate | 2007 or earlier | No |
REGEXEXTRACT | Pulls out the part of a string matching a regular expression, such as digits or an email address. More powerful than MID/FIND for messy text. | Advanced | 365 only | Yes |
REGEXREPLACE | Replaces anything matching a pattern, for example stripping every non-digit from a phone number. Handles cleanup jobs SUBSTITUTE cannot. | Advanced | 365 only | No |
REGEXTEST | Checks whether text fits a pattern, handy for validating IDs, emails or postal codes. Use it inside IF or FILTER. | Advanced | 365 only | No |
REPLACE | Swaps out characters at a known position for new text. Use SUBSTITUTE instead when you know the text rather than the position. | Intermediate | 2007 or earlier | No |
REPLACEB | Like REPLACE but counts bytes, with double-byte characters taking two. It differs from REPLACE only in East Asian language settings. | Expert | 2007 or earlier | No |
REPT | Repeats text a set number of times. Used for quick in-cell bar charts or padding values with leading characters. | Intermediate | 2007 or earlier | No |
RIGHT | Takes characters from the end of a string, such as a file extension or last four digits of an ID. | Beginner | 2007 or earlier | No |
RIGHTB | Like RIGHT but counts in bytes. Differs from RIGHT only in East Asian language settings. | Expert | 2007 or earlier | No |
SEARCH | Finds where text appears inside other text, ignoring case and allowing wildcards. The forgiving cousin of FIND. | Beginner | 2007 or earlier | No |
SEARCHB | Like SEARCH but counts in bytes. Differs from SEARCH only in East Asian language settings. | Expert | 2007 or earlier | No |
SUBSTITUTE | Replaces specific text with other text wherever (or only the nth time) it appears. Workhorse for cleaning imports, e.g. removing dashes or non-breaking spaces. | Beginner | 2007 or earlier | No |
T | Returns text as is and blanks out anything that is not text. It is rarely needed in day-to-day work. | Expert | 2007 or earlier | No |
TEXT | Converts a number or date into text in the layout you choose. Essential when combining numbers or dates with words in a sentence. | Beginner | 2007 or earlier | No |
TEXTAFTER | Returns everything after a chosen delimiter. A cleaner replacement for RIGHT/FIND combinations when splitting names, paths or emails. | Intermediate | 2024 | No |
TEXTBEFORE | Returns everything before a chosen delimiter, for example the username in an email address. Easier than LEFT with FIND. | Intermediate | 2024 | No |
TEXTJOIN | Joins many values or whole ranges with a separator you choose and optionally skips blanks. The go-to for building comma-separated lists. | Beginner | 2019 | No |
TEXTSPLIT | Breaks text into separate cells across columns and/or rows. A formula-based alternative to Text to Columns that updates when the source changes. | Intermediate | 2024 | Yes |
TRANSLATE | Translates text between languages using an online service. Useful for quickly scanning feedback in another language. | Advanced | 365 only | No |
TRIM | Removes leading and trailing spaces and collapses repeated spaces between words to one. First step in cleaning names and imported data. | Beginner | 2007 or earlier | No |
UNICHAR | Returns any Unicode character from its number, including symbols and accented letters. A wider version of CHAR. | Intermediate | 2013 | No |
UNICODE | Returns the Unicode number of the first character. Handy for finding mystery characters that TRIM misses. | Intermediate | 2013 | No |
UPPER | Converts all letters to uppercase. Useful for standardising codes before lookups. | Beginner | 2007 or earlier | No |
VALUE | Converts number-like text into a real number so it can be used in math. Often fixes numbers imported as text. | Intermediate | 2007 or earlier | No |
VALUETOTEXT | Converts any value into text. Strict mode adds quotes so you can tell text from numbers. | Advanced | 2021 | No |
Other categories
- Lookup and reference functions
43 functions
- Math and trig functions
80 functions
- Logical functions
19 functions
- Date and time functions
25 functions
- Statistical functions
111 functions
- Financial functions
56 functions
- Information functions
21 functions
- Database functions
12 functions
- Engineering functions
54 functions
- Web functions
3 functions
- Compatibility functions
41 functions
- Cube functions
7 functions
- Add-in and automation functions
5 functions