Text Manipulationbeginner
Convert text to proper case with PROPER
=PROPER(A2)
Excel how-to guide
How to trim remove spaces in Excel
Clean up imported data where names, product codes, or addresses have inconsistent spacing that breaks VLOOKUP matches or pivot groupings.
Step-by-step instructions
How to trim remove spaces — 3 steps
1
Reference the cell containing the text with unwanted spaces.
2
TRIM removes all leading and trailing spaces and reduces any internal runs of spaces to a single space.
3
Copy the formula down, then paste-as-values to lock in the cleaned text.
Example data
Worked example
| Raw Name | Cleaned Name |
|---|---|
| John Smith | John Smith |
| Alice Brown | Alice Brown |
| Bob | Bob |
Common mistakes
Errors to watch out for
TRIM does not remove all spaces
TRIM removes standard spaces (ASCII 32) but not non-breaking spaces (ASCII 160) common in web-pasted data. Use SUBSTITUTE(TRIM(A2),CHAR(160),"") to handle both.
Numbers are affected unexpectedly
TRIM always returns a text string. If the source cell is a number with extra formatting, the result may be stored as text. Use VALUE(TRIM(A2)) to convert back to a number.
FAQ
Frequently asked questions
How do I clean an entire column at once?
Add a helper column with =TRIM(A2), copy it down, copy the results, and paste-as-values over the original column. Then delete the helper column.
Describe your exact spreadsheet problem — get the formula in seconds.
Open AI Formula Generator — FreeRelated how-to guides
Continue learning.
Data Cleaningbeginner
Remove non-printable characters with CLEAN
=CLEAN(A2)