Excel Text Functions: TRIM, LEFT, MID, TEXTJOIN and More

Last updated: November 2026 · Works in Excel for Microsoft 365, Excel 2021 and Excel 2019 (TEXTSPLIT needs Microsoft 365)

Data rarely arrives clean. Customer names come in capital letters, product codes have stray spaces, and a bank statement crams the date, reference and description into one column. Excel's text functions let you tidy, split and combine text with formulas, so the work is repeatable next month when a fresh export arrives. This guide covers the functions you will use most, with examples taken from everyday office data.

Cleaning text with TRIM and CLEAN

TRIM removes spaces from the start and end of text and reduces any run of spaces inside it to a single space. =TRIM(A2) turns "  Al   Noor  Trading " into "Al Noor Trading". Extra spaces are the most common reason that lookups return #N/A and that SUMIFS totals come out short, so TRIM is often the first step in tidying data.

CLEAN removes non-printing characters such as line breaks that come from other systems. Combine them: =TRIM(CLEAN(A2)).

Text copied from websites often contains a non-breaking space (character 160) that TRIM does not remove. Replace it first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Changing case with UPPER, LOWER and PROPER

PROPER is not perfect. It also capitalises letters after apostrophes and can produce results like "Mcdonald", so check names after converting.

Extracting characters with LEFT, RIGHT and MID

The results are text, even when they look like numbers. To use "2026" in a calculation, convert it with =VALUE(MID(A2,5,4)) or simply =--MID(A2,5,4).

Finding positions with FIND and SEARCH

Fixed positions only work when every value has the same layout. When the layout varies, find the position of a separator first. =FIND("@",B2) returns the position of the @ sign in an email address. You can then take everything before it:

=LEFT(B2,FIND("@",B2)-1)

and everything after it, which is the company domain:

=MID(B2,FIND("@",B2)+1,100)

FIND is case-sensitive; SEARCH works the same way but ignores case and allows wildcards. Both return #VALUE! when the text is not found, so =ISNUMBER(SEARCH("laptop",D2)) is a reliable way to test whether a description contains a word.

Replacing text with SUBSTITUTE and REPLACE

To format a local UAE mobile number in international style, you could use ="+971 "&MID(SUBSTITUTE(A2,"-",""),2,9), which drops the leading zero.

Joining text with &, CONCAT and TEXTJOIN

The ampersand joins pieces of text: =B2&" "&C2 combines a first and last name with a space between. CONCAT does the same with a list of items. TEXTJOIN is the most powerful because it adds a separator for you and can skip empty cells:

=TEXTJOIN(", ",TRUE,D2:H2)

This joins an address spread over five columns into one line, separated by commas, with no doubled commas where a part is blank. It also works with conditions in Microsoft 365, for example listing every product a customer bought: =TEXTJOIN(", ",TRUE,FILTER(Sales[Product],Sales[Customer]=K2)).

Splitting text into columns

For a one-off split, select the column and use Data > Text to Columns. Choose Delimited, tick the separator (comma, space or other) and click Finish. Make sure there are empty columns to the right, because the results overwrite whatever is there.

In Microsoft 365, TEXTSPLIT does the same with a formula that updates automatically: =TEXTSPLIT(A2,"-") spreads "DXB-2026-0451" across three cells. TEXTBEFORE and TEXTAFTER are simpler alternatives to the FIND method above: =TEXTBEFORE(B2,"@") and =TEXTAFTER(B2,"@").

For splitting large imports that you repeat every month, Power Query is even better, because it remembers every cleaning step.

Formatting numbers as text with TEXT

When you join a number or date to text, Excel loses its formatting. ="Total: "&B10 may show "Total: 15234.5". The TEXT function fixes this by applying a format code:

Flash Fill: text cleaning without formulas

For quick jobs, Flash Fill can recognise a pattern from one or two examples. Next to a column of full names, type the first name for the first row, then press Ctrl + E. Excel fills the rest of the column following the same pattern. It works for extracting, combining and reformatting. The results are fixed values rather than formulas, so Flash Fill will not update when the source data changes. Always scan the results, because it can misread an unusual row.

Common mistakes to avoid

Summary

Back to: all Excel tutorials.