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
=UPPER(A2)converts to capital letters, useful for codes and reference numbers.=LOWER(A2)converts to small letters, useful for email addresses.=PROPER(A2)capitalises the first letter of each word, turning "MOHAMMED AL RASHID" into "Mohammed Al Rashid".
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
=LEFT(A2,3)returns the first three characters, for example the branch code "DXB" from "DXB-2026-0451".=RIGHT(A2,4)returns the last four characters, "0451".=MID(A2,5,4)returns four characters starting at position 5, "2026".=LEN(A2)returns the number of characters, handy for checking that every code has the right length.
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
=SUBSTITUTE(A2,"-","")removes every hyphen, turning "050-123-4567" into "0501234567".=SUBSTITUTE(A2,"Ltd","LLC")replaces one word with another.=REPLACE(A2,1,3,"SHJ")replaces the first three characters with "SHJ", whatever they were.
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:
="Total: AED "&TEXT(B10,"#,##0.00")shows "Total: AED 15,234.50".="Invoice date: "&TEXT(C2,"dd mmm yyyy")shows "Invoice date: 20 Nov 2026".=TEXT(A2,"00000")pads a number with leading zeros, turning 451 into "00451".
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
- Forgetting that LEFT, MID and RIGHT return text, which will not add up until converted.
- Using fixed positions when values have different lengths; find the separator instead.
- Overwriting data with Text to Columns; leave empty columns first.
- Joining dates without TEXT, which shows serial numbers like 46346.
Summary
- TRIM and CLEAN remove spaces and hidden characters that break lookups.
- LEFT, RIGHT and MID extract text; FIND and SEARCH locate separators.
- SUBSTITUTE replaces text; TEXTJOIN combines ranges with a separator.
- TEXT keeps number and date formats when joining text.
- Flash Fill (Ctrl + E) handles quick one-off patterns.
Back to: all Excel tutorials.