Text Formula Examples in Excel: Clean, Extract, Combine

Excel text formulas let you combine words, extract part of a value, standardize capitalization, and clean imported text. For example, with a first name in A2 and a surname in B2, enter =A2&" "&B2 in C2 to return a full name with a space between the names.

What Excel text formulas do

Text formulas take text as input, change or inspect it, and return a result in a cell. The source might be a name, an address, a product code, a date stored as text, or a number that needs to display with leading zeros. Some formulas work with whole cell values; others find or return a specified number of characters.

In Excel for Microsoft 365 on Windows, formulas begin with an equals sign, and commas separate function arguments. The ampersand is an operator for joining text. Formula results usually update when their source cells change, which makes formulas useful for repeatable cleanup and reporting.

A key distinction is that a formula can return text without changing the original source cell. You can keep imported data in one column and place the cleaned or reformatted result in another. This preserves the source for checking and makes it easier to spot mistakes.

Combine text from multiple cells

Use the ampersand to join a small number of pieces of text. With the first name Maya in A2 and the surname Chen in B2, enter =A2&" "&B2 in C2. The result is Maya Chen. The text in quotation marks supplies the separating space; without it, the result would be MayaChen.

For more than a few pieces, CONCAT or TEXTJOIN can be easier to read. With a city in A2, a comma in B2, and a state abbreviation in C2, enter =CONCAT(A2,B2," ",C2) in D2. If A2 contains Portland, B2 contains a comma, and C2 contains OR, the result is Portland, OR. CONCAT joins its supplied items in order and does not add spaces or punctuation automatically.

TEXTJOIN is useful when a range may contain blank cells. With values in A2:C2, such as North, a blank cell, and West, enter =TEXTJOIN(", ",TRUE,A2:C2) in D2. The result is North, West. The first argument sets the separator, and TRUE tells the function to ignore empty cells. If you want to retain an empty position, use FALSE instead; that can leave adjacent separators.

When filling a formula down a column, Excel adjusts relative references such as A2 and B2 to A3 and B3. To keep a reference fixed while copying, add dollar signs, as in $A$2. Check a few rows after filling to confirm that the intended source cells are being joined.

Extract characters, words, and codes

LEFT returns characters from the start of a text value, RIGHT returns characters from the end, and MID returns characters from a position you specify. These functions are handy when records follow a consistent pattern, such as a code with a fixed prefix and numeric section.

With the full name Maya Chen in A2, enter =LEFT(A2,FIND(" ",A2)-1) in B2 to return Maya. FIND locates the space, and subtracting one excludes it from the returned text. This example assumes there is one space between the first and last name. Names with multiple spaces or no space need a different approach or prior cleanup.

With the same value in A2, enter =RIGHT(A2,LEN(A2)-FIND(" ",A2)) in C2 to return Chen. LEN counts all characters, while FIND identifies where the separating space occurs. This simple formula is intended for a two-part name with one separating space, not every possible naming convention.

With the product code AB-204-X in A2, enter =MID(A2,4,3) in B2 to return 204. MID starts at character four and returns three characters. It works well when the code format is consistent; if the number of characters varies, searching for delimiters is usually more reliable than assuming fixed positions.

FIND is case-sensitive, whereas SEARCH is not. If the text you are locating may appear in different capitalization, SEARCH can be a better match. Both return a position number, so a search for text that is not present produces an error. Use IFERROR when an absent match is an expected possibility and you want a clearer fallback value.

Clean spaces and unwanted characters

Imported data often contains extra spaces or characters that are difficult to see. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. With the text ” Maya Chen ” in A2, enter =TRIM(A2) in B2. The result is Maya Chen. TRIM does not remove every kind of whitespace, including nonbreaking spaces that can arrive from web pages.

CLEAN removes certain nonprinting characters from text. With a value in A2 that contains unwanted nonprinting characters, enter =CLEAN(A2) in B2. Its result depends on the actual contents of A2, so there is no single fixed output to report. CLEAN is not a general-purpose remover of every unusual Unicode character, and it does not replace TRIM for ordinary excess spaces.

If copied text contains nonbreaking spaces, replace that character before trimming. With the affected text in A2, enter =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) in B2. SUBSTITUTE changes character 160 to a regular space, and TRIM then removes extra ordinary spaces. This often helps with web-copied text, although the exact cleanup needed depends on which characters the source contains.

SUBSTITUTE can also remove or replace a known character. With the code AB-204-X in A2, enter =SUBSTITUTE(A2,"-","") in B2 to return AB204X. To replace rather than remove a character, put the replacement text between the last pair of quotation marks. Review the result before using it for matching: removing punctuation may make distinct codes look alike.

Change capitalization consistently

UPPER changes letters to uppercase, LOWER changes them to lowercase, and PROPER capitalizes the first letter of each word while making the remaining letters lowercase. With the text mAYA cHEN in A2, enter =PROPER(A2) in B2 to return Maya Chen. With the same input, UPPER returns MAYA CHEN and LOWER returns maya chen.

These functions apply general capitalization rules; they do not know every person’s preferred spelling or every brand’s styling. PROPER can alter names and acronyms in ways that are not wanted, such as turning a short uppercase abbreviation into mixed case. Check the output against the source or an approved reference list when exact spelling matters.

Display numbers and dates as text

TEXT converts a numeric value into text using a format pattern. With the number 427 in A2, enter =TEXT(A2,"00000") in B2 to return the text value 00427. This is useful for displaying fixed-width identifiers, but the result is text, not a number. Arithmetic and sorting may therefore behave differently from a numeric value.

With a valid Excel date in A2 representing January 5, 2026, enter =TEXT(A2,"mmm d, yyyy") in B2 to return Jan 5, 2026 in an English-language format. The displayed wording can depend on Excel’s regional settings. If the date is actually stored as text rather than as an Excel date value, TEXT may not format it as expected.

Use number formatting instead of TEXT when you only want to change how a number or date appears in its existing cell and still need it to behave as a number. TEXT is appropriate when you need to combine the formatted value with words or place a formatted representation into a text string. For example, a report label may need a date embedded alongside a heading.

Choose the right formula for the task

Start with the shape of the input and the result you need. A short decision guide can prevent unnecessary complexity:

To join a few cells with custom punctuation, use the ampersand or CONCAT.
To join a range while choosing a separator and handling blanks, use TEXTJOIN.
To take characters from a known position or fixed length, use LEFT, RIGHT, or MID.
To find a word or delimiter within text, use FIND or SEARCH together with an extraction function.
To standardize spaces, nonprinting characters, or known symbols, consider TRIM, CLEAN, and SUBSTITUTE.
To change letter case, use UPPER, LOWER, or PROPER, then review names and abbreviations.
To show a number or date in a text string, use TEXT; to retain numeric behavior, consider cell number formatting instead.

For data with highly variable formats, a single compact formula can become hard to maintain. In Microsoft 365, newer functions such as TEXTSPLIT, TEXTBEFORE, and TEXTAFTER can help separate text by delimiters, but they are not available in every older Excel release. If a workbook will be opened in older versions, check compatibility before relying on them. For one-time data cleanup, Excel’s Text to Columns command or Power Query may be more practical than a long formula.

Common formula problems and checks

A formula that returns an error may be making a valid assumption that the source data does not meet. For example, an extraction formula using FIND for a space cannot locate a space in a one-word value. Before adding error handling, inspect the source and decide what the correct result should be for missing or irregular information.

If a formula displays as text instead of calculating, check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Set the cell to General, then re-enter the formula. Also check that calculation is enabled and that the formula has balanced parentheses and quotation marks. Excel may use a different list separator in some regional configurations, so a workbook configured for semicolons may require that separator in place of commas.

When a text formula returns an unexpected result, test its parts in spare cells. For an extraction formula, first check the position returned by FIND, then check the character count returned by LEN. This makes it easier to see whether the issue is the source text, the delimiter, or the number of characters requested.

Finally, remember that formulas return calculated results, not a replacement for their source values. If you need to send a file with fixed outputs, copy the results and use Paste Special > Values. Keep a copy of the original data first, especially when the formulas are part of a cleanup process that may need to be reviewed or reversed.

Frequently Asked Questions

What is the simplest text formula in Excel?
For joining two cells with a space, use the ampersand, such as =A2&" "&B2. Put the source values in A2 and B2. For other tasks, the simplest formula depends on whether you are joining, extracting, cleaning, or reformatting text.

How do I combine text without adding spaces?
Join the cell references directly, such as =A2&B2, or use CONCAT with the references as arguments. Neither method inserts punctuation or spaces automatically. Add a quoted space or other separator only where you want it to appear.

Why does TRIM not remove all spaces?
TRIM targets ordinary space characters. Text copied from websites or other systems can contain nonbreaking spaces or other unusual characters that TRIM does not remove. SUBSTITUTE with CHAR(160) can replace common nonbreaking spaces before TRIM runs, but other characters may require a different cleanup step.

Can an Excel text formula keep leading zeros?
Yes. If the source is a number such as 427 and it must display as five digits, TEXT with a five-zero format pattern returns 00427 as text. If the zeros are part of an identifier, it may be better to store the identifier as text from the start so Excel does not treat it as a quantity.

Why does TEXT change how a value sorts?
TEXT returns text, even when it displays digits or a date. Text may sort alphabetically rather than numerically or chronologically. Use cell number formatting when you need to change appearance while retaining a numeric value, and use TEXT when a formatted value must become part of text.

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.