Clean up messy text in Excel: TRIM, PROPER and SUBSTITUTE

Extra spaces and stray characters break lookups and counts; wrong capitals look careless. How TRIM, PROPER, UPPER, LOWER and SUBSTITUTE fix them.

Published 16 October 2026. 7 min read.

By Ali, Founder, Excel Lessons

Use TRIM to remove extra spaces. Use PROPER, UPPER or LOWER to fix capitals. Use SUBSTITUTE to remove or replace stray characters.

Put each fix in a new column next to the data. Check the result. Then paste it back over the original as values.

Which mess breaks what

Here are 5 cities in A2:A6. Four of them are Leeds. But =COUNTIF(A2:A6,"Leeds") returns 3.

Row	A	What is in the cell
1	City
2	Leeds	Leeds
3	Leeds	Leeds, with a space at the end
4	LEEDS	LEEDS, all capitals
5	York	York
6	leeds	leeds, all small letters

The capitals are not the problem. COUNTIF ignores case, so LEEDS and leeds both count. The missing one is A3. Its space at the end makes it a different text.

=A2=A4 returns TRUE. Leeds and LEEDS are equal to Excel.
=A2=A3 returns FALSE. The space is a character, and you cannot see it.

VLOOKUP, XLOOKUP and MATCH work the same way. A space breaks the match and returns #N/A. A capital does not. So clean the spaces first. Fix the capitals because the text must look right in a report.

TRIM: extra spaces

TRIM takes one argument, the text. It does two things:

It removes every space at the start and at the end.
It changes 2 or more spaces in a row into 1 space.

A2 holds ashworth & co, with a space before it and a space after it. =TRIM(A2) returns ashworth & co with no outside spaces.

A3 holds Ford Street, with 2 spaces between the words. =TRIM(A3) returns Ford Street with 1 space.

The cell looks the same before and after. So measure it. LEN counts every character, spaces too. =LEN(A2)-LEN(TRIM(A2)) returns 2 for A2. That is the 2 spaces TRIM removed. If it returns 0, the cell has no extra normal spaces.

When TRIM does not work

Text copied from a web page can hold a different space. It is the non-breaking space, character 160. It looks like a normal space. TRIM only removes the normal space, character 32.

So the check above returns 0, and the lookup still fails. To test the last character, type =UNICODE(RIGHT(A2)). It returns 160 if that character is a non-breaking space.

Fix: =TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

SUBSTITUTE changes each non-breaking space into a normal space. Then TRIM removes the normal ones.

UPPER, LOWER and PROPER: capitals

UPPER turns each letter into a capital. LOWER turns each letter into a small one. PROPER gives each word a capital first letter, and makes the rest small.

Row	A: before	Formula in B	B shows
2	ANNA-MARIE FOLEY	=PROPER(A2)	Anna-Marie Foley
3	ADA@EXAMPLE.COM	=LOWER(A3)	ada@example.com
4	gb-2024-0031	=UPPER(A4)	GB-2024-0031
5	mcdonald	=PROPER(A5)	Mcdonald
6	van der berg	=PROPER(A6)	Van Der Berg
7	3rd floor	=PROPER(A7)	3Rd Floor

UPPER and LOWER are safe. They do the same thing to every letter. Use UPPER for codes and LOWER for email addresses.

PROPER needs a check by eye. It puts a capital after anything that is not a letter. A hyphen, a space, an apostrophe and a number all count.

That gives Anna-Marie and O'Brien, which are right. It also gives Mcdonald, Van Der Berg and 3Rd Floor, which are wrong. PROPER knows letters, not names.

SUBSTITUTE: stray characters

SUBSTITUTE takes the text, the old part and the new part. =SUBSTITUTE(A2,"/","-") changes every slash into a hyphen. So PO/2024/0088 becomes PO-2024-0088.

To remove a character, replace it with nothing. You type nothing as 2 quotation marks side by side: "".

A2 holds (0161) 496 0208. To keep only the digits, put one SUBSTITUTE inside another:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ","")

It returns 01614960208. The inner SUBSTITUTE runs first and removes the opening bracket. The next one removes the closing bracket. The outer one removes the spaces.

A 4th argument says which one to change. A2 holds INV-2024-0031. =SUBSTITUTE(A2,"-","/",2) returns INV-2024/0031. Only the 2nd hyphen changes.

SUBSTITUTE cares about capitals

A lookup does not care about capitals. SUBSTITUTE does. =SUBSTITUTE(A2,"inv","REF") returns INV-2024-0031, unchanged. There is no error.

Type the old part with the same capitals as the data. =SUBSTITUTE(A2,"INV","REF") returns REF-2024-0031. FIND also cares about capitals.

Cleaned numbers are still text

A2 holds 1 250, with a space between the 1 and the 2. =SUBSTITUTE(A2," ","") returns 1250. But the result is text, not a number.

=ISNUMBER(SUBSTITUTE(A2," ","")) returns FALSE. SUM on a column of these results skips them. The total is wrong and there is no error message.

Fix: =VALUE(SUBSTITUTE(A2," ","")) returns the number 1250.

Do not do this to a phone number. It starts with 0, and a number drops the 0. Keep phone numbers as text.

Two fixes in one formula

Put one function inside another, and Excel works out the inner one first. For the ashworth & co cell, =PROPER(TRIM(A2)) returns Ashworth & Co. Put TRIM on the inside as a habit.

Keep the clean text, lose the formulas

Type the cleaning formula in a new column, next to the data. Fill it down.
Check a few rows with LEN, and read the PROPER results by eye.
Copy the new column.
Select the first cell of the original column. Then click Home → Paste → Paste Values.
Delete the new column.

Paste Values keeps the results and drops the formulas. If you skip it and delete the original column, the formulas show #REF!.

Questions people ask

Can I clean text without a formula?

Yes, with Find and Replace. Press Ctrl+H. It changes the cells themselves, so there is no new column. It ignores capitals unless you click Options and tick Match case.

How do I remove a line break inside a cell?

A line break is character 10. =TRIM(SUBSTITUTE(A2,UNICHAR(10)," ")) changes each break into a space, then removes extra spaces.

Does VLOOKUP care about capitals?

No. VLOOKUP, XLOOKUP, MATCH and COUNTIF all ignore case. If you need case to matter, compare with =EXACT(A2,B2). It returns TRUE only when every capital is the same.