TRIM and text cleaning in Excel

Q: How do I capitalize only the first letter of each word?

Use =PROPER(A2). It puts a capital at the start of each word and the rest in lower case.

Last updated October 30, 2026

TRIM removes the spaces at the start and end of a text and shrinks every run of spaces between words to one. For example, =TRIM(A2) turns " Northwind Foods " into "Northwind Foods". Pair it with PROPER, UPPER or LOWER to fix capitals and SUBSTITUTE to replace characters.

TRIM syntax

=TRIM(text)

text: The text to clean: usually a cell reference such as A2, or another formula such as SUBSTITUTE(A2,"_"," "). TRIM only removes the regular space; the non-breaking space found in text copied from web pages stays.
Related functions: PROPER(text), UPPER(text) and LOWER(text) change the case. SUBSTITUTE(text, old_text, new_text, [instance_num]) replaces every old_text, or only the one given by instance_num.

Practice TRIM here

Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.

Exercise 1 of 1

Remove extra spaces from customer names

The names in column A came from an export with extra spaces. In B2, use TRIM to clean the name in A2 so it reads Northwind Foods, with one space between the words and none around them.

Imported name	Clean name
Northwind Foods
Bluebird Studio
Harbor Logistics
Maple Dental

How to clean text, step by step

Insert an empty column next to the imported data, so the original stays untouched.
In the first row of the new column, type =TRIM(, click the cell to clean, close the bracket and press Enter.
Wrap it in PROPER, UPPER or LOWER if the capitals also need fixing, such as =PROPER(TRIM(A2)).
Copy the formula down. To replace the original data, copy the clean column and use Paste Special > Values.

TRIM, PROPER and SUBSTITUTE examples

Remove extra spaces

=TRIM(A2)

Turns " Northwind Foods " into Northwind Foods: no spaces at the ends, one between words.

Fix the capitals of a name

=PROPER(A2)

Turns oLIVIA rEED into Olivia Reed. UPPER would give OLIVIA REED and LOWER olivia reed.

Remove every space

=SUBSTITUTE(A2," ","")

Turns TRK 4F8 V42 0312 3456 into TRK4F8V4203123456. TRIM can’t do this, as it keeps single spaces between words.

Build an email address from a name

=LOWER(SUBSTITUTE(TRIM(A2)," ","."))&"@example.com"

Turns " Olivia Reed" into olivia.reed@example.com. TRIM goes first, otherwise the double space becomes two dots.

Common text cleaning problems and how to fix them

Problem	Cause	Fix
Spaces are still there	The text holds non-breaking spaces (character 160), common in data copied from web pages. TRIM only removes the regular space, character 32.	Turn them into regular spaces first: `=TRIM(SUBSTITUTE(A2,CHAR(160)," "))`.
A lookup still finds no match	Only one side of the lookup was cleaned, or the clean column isn’t the one the lookup reads.	Clean both the value you look for and the table, or wrap it in the lookup: `VLOOKUP(TRIM(E2),…)`.
Odd capitals after PROPER	PROPER capitalizes every letter that follows a non-letter, so McDONALD becomes Mcdonald and 2nd becomes 2Nd.	Fix the few exceptions by hand, or with SUBSTITUTE on the result.
Strange symbols or line breaks	The text holds non-printing characters, such as line breaks from another system. TRIM leaves them in place.	Use CLEAN, which removes non-printing characters: `=TRIM(CLEAN(A2))`. This practice grid doesn’t grade CLEAN.

TRIM vs SUBSTITUTE vs CLEAN

TRIM removes extra spaces but keeps one between words. SUBSTITUTE replaces every occurrence of a character, so SUBSTITUTE(A2," ","") removes all spaces, inner ones included. PROPER, UPPER and LOWER only change the case. CLEAN removes non-printing characters such as line breaks. Find and Replace (Ctrl + H) changes the cells themselves in one go, while these functions leave the original data intact and update when it changes.

Frequently asked questions

Why doesn’t TRIM remove all the spaces?

TRIM keeps one space between words on purpose. To remove every space, use =SUBSTITUTE(A2," ",""). If spaces at the ends survive, they are probably non-breaking spaces: use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

How do I remove only the leading spaces?

TRIM removes leading and trailing spaces together and shrinks inner runs of spaces. There is no built-in function for the start only, but TRIM is what you need in most imports.

How do I capitalize only the first letter of each word?

Use =PROPER(A2). It puts a capital at the start of each word and the rest in lower case.

Can I clean the data in place, without a new column?

Not with a formula, since a formula can’t change the cell it reads. Clean into a new column, then copy it and paste it over the original with Paste Special > Values.

Keep practising

All 5 text cleaning exercises, with explanations and progress tracking.

Open the TRIM practice set