Excel Text Functions List: Names, Uses, and Examples

Excel’s text functions help you extract, combine, clean, compare, format, and convert text or values. The main functions include LEFT, RIGHT, MID, TEXTBEFORE, TEXTAFTER, TEXTSPLIT, TRIM, CLEAN, SUBSTITUTE, TEXTJOIN, TEXT, VALUE, and NUMBERVALUE; the right choice depends on what your data looks like and what result you need.

What Excel text functions do

Text functions are formulas that work with text strings or return text. Some manipulate words and characters, while others turn numbers or dates into text, or turn text that looks like a number into a numeric value. They are useful for cleaning imported data, separating names or codes, building labels, and preparing information for reports.

Excel stores dates and numbers as values, even when it displays them with currency symbols or date formatting. That distinction matters: a cell that displays as 1,234.50 may contain a number, while a text function can produce the characters “1,234.50” as a text result. Text and numbers may look alike on the worksheet but behave differently in calculations, sorting, and matching.

The list below covers common functions and specialized ones available in Excel for Microsoft 365 on Windows. Some newer functions require Microsoft 365 or Excel 2024. Availability of the newer regular-expression functions can depend on your Microsoft 365 update channel. Function names and argument separators shown here use English-language Excel with commas.

Common Excel text functions, at a glance

These functions cover the most frequent jobs. The groupings are practical rather than strict: several functions can solve the same task, but differ in how flexible or readable they are.

Extract characters: LEFT, RIGHT, and MID return text from the start, end, or a specified position. LEN counts characters.
Find text: FIND locates a case-sensitive match; SEARCH locates a match without distinguishing uppercase and lowercase.
Replace or repeat: REPLACE changes text at a specified character position; SUBSTITUTE replaces matching text. REPT repeats text a chosen number of times.
Change letter case: UPPER, LOWER, and PROPER convert text to uppercase, lowercase, or title-style capitalization.
Clean or compare: TRIM removes extra ordinary spaces, CLEAN removes many nonprinting characters, and EXACT compares two strings with case sensitivity.
Combine text: CONCAT and TEXTJOIN combine text from multiple cells. CONCATENATE is the older function retained for compatibility.
Convert values: TEXT formats a value as text; VALUE and NUMBERVALUE convert numeric text into a number.

Microsoft 365 and Excel 2024 also include TEXTBEFORE, TEXTAFTER, and TEXTSPLIT for delimiter-based extraction or splitting. These can be easier to read than older combinations of FIND, LEFT, and MID.

Extract and locate text

LEFT and RIGHT are useful when the number of characters is fixed, such as a two-letter region code or the final four characters of an identifier. MID is for text found at a known position inside a string. LEN can help when the position depends on the string’s length. These functions return text, even if the extracted characters happen to be digits.

With an invoice code in A2 containing INV-2048, this formula returns the first three characters, INV: =LEFT(A2,3). With the same value in A2, this formula returns the four characters beginning at position five, 2048: =MID(A2,5,4). The starting position is counted from 1, not 0.

FIND and SEARCH return the position where a match begins, rather than returning the matching text itself. FIND is case-sensitive; SEARCH is not. Both can take an optional starting position, which is useful when looking for a later occurrence. If no match is found, they return an error, so a formula may need IFERROR when missing matches are expected.

For example, with INV-2048 in A2, this formula returns 4, the position of the hyphen: =FIND("-",A2). To extract text around a delimiter in a more direct way, TEXTBEFORE returns the part before it and TEXTAFTER returns the part after it. They support optional arguments for choosing an occurrence and handling case or missing delimiters.

With A2 containing INV-2048, this formula returns INV: =TEXTBEFORE(A2,"-"). TEXTBEFORE, TEXTAFTER, and TEXTSPLIT require Microsoft 365 or Excel 2024; they may not be available in older perpetual editions.

Split, replace, and combine strings

TEXTSPLIT separates a string at a chosen delimiter and spills the results into neighboring cells. It can use one delimiter to split across columns and another to split down rows. This is handy for data such as comma-separated tags or records where commas and semicolons have different meanings. Make sure the cells where the results will spill are empty.

With A2 containing red,blue;green,yellow, this formula uses commas to divide columns and semicolons to divide rows. It returns red and blue on the first row and green and yellow on the second: =TEXTSPLIT(A2,",",";").

SUBSTITUTE searches for specified text and replaces matching occurrences. REPLACE instead works from a character position and replaces a set number of characters. Use SUBSTITUTE when the target is a known character or word; use REPLACE when its position is what matters. Both can be used to standardize codes, but check whether replacing every occurrence is appropriate.

With INV-2048 in A2, this formula removes every hyphen and returns INV2048: =SUBSTITUTE(A2,"-",""). CONCAT joins text but does not offer a separator between each item automatically. TEXTJOIN lets you specify a delimiter and whether to ignore empty cells, which often makes it more convenient for lists.

With first names in A2:A4 containing Ana, Bo, and Cy, and no blank cells in that range, this formula returns Ana, Bo, Cy: =TEXTJOIN(", ",TRUE,A2:A4). CONCATENATE remains in Excel for older workbooks, but Microsoft recommends CONCAT or TEXTJOIN for new formulas.

Clean and standardize text

Imported or copied text can contain extra spaces, line breaks, or nonprinting characters. TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to a single space. CLEAN removes many nonprinting characters from text. Neither is a universal fix for every invisible character: a nonbreaking space, for example, may need to be replaced explicitly.

With a name in A2 that has ordinary extra spaces around it, this formula returns the name with excess spaces removed: =TRIM(A2). If A2 contains text with nonprinting characters, this formula removes characters that CLEAN recognizes: =CLEAN(A2). Combining the functions can help with imported text, but a stubborn character may require identifying and replacing that specific character.

UPPER, LOWER, and PROPER change letter case. PROPER capitalizes the first letter of each word and lowercases other letters, so it may not preserve the preferred styling of names, acronyms, or brand names. EXACT compares two text strings and returns TRUE only when they match character for character, including case. These functions can standardize or check text, but they do not automatically understand a person’s preferred spelling or a business’s naming rules.

Format numbers and convert text

TEXT converts a number or date to text using a format code. This is useful for creating a readable label, but the result is text and no longer behaves as the original numeric value in calculations. Keep the underlying number in a separate cell if you still need to calculate with it. Format codes use Excel’s number-format conventions, and date displays can vary with regional settings.

With a date in A2 whose value is January 15, 2026, this formula returns the text Jan 15, 2026: =TEXT(A2,"mmm d, yyyy"). The format code controls the displayed text; the formula does not change the original date in A2.

VALUE converts text that Excel recognizes as a number into a numeric value. NUMBERVALUE gives more control over the decimal and group separators, which is useful when the text was created under different regional conventions. Both can fail if the text contains unexpected characters or separators. DOLLAR and FIXED also return formatted text rather than numbers, so they are presentation functions, not replacements for number formatting on the cell itself.

With A2 containing the text 1.234,50, where the comma is the decimal separator and the period is the group separator, this formula returns the numeric value 1234.5: =NUMBERVALUE(A2,",","."). When the separators are not specified, Excel relies on its current regional interpretation, which may not match the source data.

Specialized and newer text functions

Some text functions are designed for particular data types, languages, or conversion jobs. CODE returns a numeric code for the first character, while CHAR returns the character associated with a code in Excel’s character set. UNICODE and UNICHAR provide corresponding Unicode operations. These can help inspect or construct characters, although code results depend on the character system involved.

ARRAYTOTEXT and VALUETOTEXT convert arrays or values to text representations, which can be useful when inspecting or assembling formula results. T returns its argument if it is text and an empty string otherwise; it is mainly useful in specific formula designs. ASC and DBCS convert between full-width and half-width characters in supported East Asian language settings. PHONETIC returns phonetic guide text for supported Japanese text, and BAHTTEXT converts a number to Thai text. These specialized functions are not necessary for most everyday worksheets.

Microsoft 365 also offers REGEXTEST, REGEXEXTRACT, and REGEXREPLACE in supported update channels. They use regular expressions to test for a pattern, extract matching text, or replace matching text. Regular expressions can handle variable patterns, such as a sequence of digits, but they have their own syntax and are more complex than a simple delimiter function. If one of these functions is unavailable in your Excel installation, use a supported alternative such as FIND, SEARCH, or SUBSTITUTE where it fits the task.

How to choose the right function

Start by identifying the shape of the source data and the exact result you need. A fixed character position suggests LEFT, RIGHT, or MID. A known separator suggests TEXTBEFORE, TEXTAFTER, or TEXTSPLIT. A text pattern that may vary can call for SEARCH or, in a supported version, a regular-expression function. For cleanup, decide whether the problem is ordinary spaces, nonprinting characters, or a specific unwanted substring before choosing TRIM, CLEAN, or SUBSTITUTE.

Also decide whether the output must remain a number or date. If it will be used in arithmetic or date calculations, keep it numeric and apply cell formatting. Use TEXT only when you deliberately need a text label, such as a date included in a sentence. For joining values, TEXTJOIN is often a practical choice because it accepts a delimiter and can skip blank cells; CONCAT is simpler when no delimiter or blank-handling option is needed.

Finally, consider compatibility. Newer functions can make formulas shorter, but a workbook shared with someone using an older Excel version may show an unsupported-function error. If the workbook must work in older releases, use established functions such as LEFT, MID, FIND, SUBSTITUTE, and CONCATENATE where possible, or confirm the recipient’s Excel version before relying on newer functions.

Common problems and misconceptions

A number-looking result will not calculate: It may be text. VALUE or NUMBERVALUE can convert suitable numeric text, but first check for currency symbols, spaces, and regional separators.
TRIM did not remove every space: TRIM targets ordinary spaces. Imported nonbreaking spaces or other unusual characters may need to be replaced separately.
FIND returns an error: The exact character or text may be absent, or the letter case may differ. SEARCH is case-insensitive; IFERROR can handle expected missing matches.
A split formula shows a spill error: The cells needed for the results may not be empty. Clear the obstructing cells or move the formula to an open area.
TEXT changes what a value can do: TEXT returns a text string. Use the original numeric or date value for calculations, and format the cell when you only need a different display.

A frequent misconception is that all text functions simply change how a cell looks. Some do return a changed display as text, but many create a new value in the formula cell. The source cell is not altered unless you copy results and paste them over the source. That distinction is important when a cleaned or reformatted result needs to feed later calculations.

Frequently Asked Questions

What are the most commonly used Excel text functions?
LEFT, RIGHT, MID, LEN, FIND, SEARCH, TRIM, SUBSTITUTE, TEXTJOIN, TEXT, VALUE, and UPPER or LOWER cover many routine tasks. For delimiter-based extraction and splitting in Microsoft 365 or Excel 2024, TEXTBEFORE, TEXTAFTER, and TEXTSPLIT are useful additions.

What is the difference between FIND and SEARCH?
Both return the position of matching text. FIND is case-sensitive, while SEARCH is not. If the text is missing, either function returns an error unless the formula handles that result.

What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE replaces matching text, such as every hyphen in a code. REPLACE changes a specified number of characters starting at a specified position. Choose based on whether you know what text to find or where the change must occur.

Does TEXT turn a number into a number with a different format?
No. TEXT returns text formatted according to the format code, so the result is not the original numeric value. If you only want a different appearance in a cell, use Excel’s number-format controls instead.

Why does TEXTSPLIT not work in my Excel?
TEXTSPLIT requires Microsoft 365 or Excel 2024. If you use an older version, the function may not be recognized. You can sometimes split data with Text to Columns, or build a formula with older functions such as FIND, LEFT, and MID, depending on the data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.