Practice free

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

All text functions

FunctionWhat it doesLevelSinceSpills?
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