Excel SUBSTITUTE, TRIM and PROPER: clean up messy data
Data pasted from forms, web pages or other systems rarely arrives clean. Stray spaces, names in capitals, phone numbers full of dashes and prices Excel will not add up are all fixed with a handful of functions.
In short: =TRIM(A2) removes extra spaces, =PROPER(A2) capitalizes each word, =SUBSTITUTE(A2,"-","") strips every dash and =VALUE(A2) turns a number stored as text into a real number.
Try it now
These names were typed into a web form, and everyone typed them differently. Turn them into a clean list you can sort, look up and use for a mail merge.
Task These names come from a web form: some in lower case, some in capitals, with stray spaces. In B2, write a formula that returns a clean name with no extra spaces and each word capitalized (Jana Weber). The formula is filled down to B7.
| A | B | C | |
|---|---|---|---|
| 1 | Name (raw) | Name cleaned | Department |
| 2 | jana weber | Sales | |
| 3 | MURAT KAYA | Purchasing | |
| 4 | lena hoffmann | Finance | |
| 5 | Tom Schulz | Sales | |
| 6 | sara KLEIN | HR | |
| 7 | ali demir | Logistics |
The cleanup toolkit
| Function | What it does | Example |
|---|---|---|
| TRIM | Removes leading and trailing spaces and leaves one space between words | =TRIM(" Anna Weber ") returns "Anna Weber" |
| PROPER | First letter of each word upper case, the rest lower case | =PROPER("ANNA WEBER") returns "Anna Weber" |
| UPPER, LOWER | Everything in capitals or everything in lower case | =LOWER("Info@Company.com") returns "info@company.com" |
| SUBSTITUTE | Swaps a specific piece of text for another | =SUBSTITUTE("555-0142","-","") returns "5550142" |
| VALUE | Converts a number stored as text into a number | =VALUE("12.50") returns 12.5 |
| CLEAN | Removes nonprinting control characters such as line breaks from exports | =CLEAN(A2) |
Nest them to fix several problems at once. In =PROPER(TRIM(A2)), TRIM runs first and PROPER works on its result.
Replace or remove characters with SUBSTITUTE
SUBSTITUTE looks for a string inside a text and replaces it. To delete something, replace it with an empty string, written as two double quotes with nothing between them.
| Argument | Meaning | In the example |
|---|---|---|
| text | The cell with the original text | B2 |
| old_text | What to replace | "-" |
| new_text | What goes in its place | "" (nothing) |
| instance_num | Optional: replace only the nth occurrence | left out, so all of them |
Task The phone system import only accepts numbers without dashes. In C2, write a formula that removes every dash from the number in B2. The formula is filled down to C7.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Phone | Phone cleaned |
| 2 | Jana Weber | 0151-2384-907 | |
| 3 | Murat Kaya | 030-5571-22 | |
| 4 | Lena Hoffmann | 0172-9930-415 | |
| 5 | Tom Schulz | 040-338-1290 | |
| 6 | Sara Klein | 0160-4402-873 | |
| 7 | Ali Demir | 089-2140-66 |
To strip several different characters, wrap one SUBSTITUTE inside another: =SUBSTITUTE(SUBSTITUTE(B2,"-","")," ","") removes the dashes, then the spaces. Each extra character adds one more layer.
For a one-off cleanup you can skip formulas and press Ctrl+H for Find and Replace. That changes the cells in place. A formula keeps the original data intact and also cleans rows you add later.
Convert numbers stored as text
Figures exported from web shops, banking portals or CSV files often land in Excel as text. The giveaways: they sit on the left of the cell, show a small green triangle in the corner, and SUM ignores them. A common cause is the decimal separator. A German system writes 12,50 where Excel with US settings expects 12.50.
Task These prices come from a German accounting system and arrive as text with a decimal comma in column B. Excel with US settings will not calculate with them. In C2, write a formula that replaces the comma with a point and turns the result into a real number. The formula is filled down to C7.
| A | B | C | |
|---|---|---|---|
| 1 | Item no. | Price (export) | Price as number |
| 2 | A-118 | 12,50 | |
| 3 | A-104 | 4,95 | |
| 4 | A-131 | 129,00 | |
| 5 | A-109 | 0,89 | |
| 6 | A-125 | 18,40 | |
| 7 | A-112 | 56,75 |
When an amount also carries thousands separators, as in "1.234,50", do it in two steps: =VALUE(SUBSTITUTE(SUBSTITUTE(B2,".",""),",",".")) drops the dots first and then turns the comma into a point. NUMBERVALUE does the same in one go because you tell it which separators the text uses: =NUMBERVALUE(B2,",","."). The second argument is the decimal separator in the text, the third the group separator.
Fix capitalization
PROPER is great for names and towns, but it capitalizes after every space, hyphen and apostrophe. "o'brien" becomes "O'Brien", which is fine, yet "mcdonald" becomes "Mcdonald" and "van der berg" becomes "Van Der Berg". Fix those few by hand afterwards. Use LOWER for email addresses and user names, UPPER for product codes and postcodes.
Worth knowing when comparing: VLOOKUP, COUNTIF and a plain equals test ignore case, so "LONDON" matches "London". SUBSTITUTE and FIND do not, so =SUBSTITUTE(A2,"inc","Inc") leaves "INC" untouched.
Common problems when cleaning data
TRIM leaves some spaces behind
TRIM only removes the regular space character. Text copied from web pages often contains nonbreaking spaces (character 160), which look identical. Swap them first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). This is also a classic reason why a VLOOKUP returns #N/A although the values look the same.
VALUE returns #VALUE!
Something in the text still stops Excel from reading it as a number, typically a currency sign, a unit such as "kg" or the wrong separator. Remove it with SUBSTITUTE first and VALUE will work.
The cleaned column breaks when you delete the original
Your cleaned values are formulas that point at the raw column. Delete that column and they show #REF!. Copy the cleaned column, then right-click and choose Paste Options > Values. Now the raw data can go.
Leading zeros disappear
SUBSTITUTE returns text, so phone numbers and ZIP codes keep their leading 0. Running them through VALUE turns them into numbers and the 0 is gone. Leave identifiers like these as text.
SUBSTITUTE and TRIM in German Excel
German Excel translates the function names and separates arguments with semicolons. Because the comma is the German decimal separator, the conversion runs the other way round there: =WERT(WECHSELN(B2;".";",")).
| English | German |
|---|---|
| SUBSTITUTE, REPLACE | WECHSELN, ERSETZEN |
| TRIM, CLEAN | GLÄTTEN, SÄUBERN |
| UPPER, LOWER, PROPER | GROSS, KLEIN, GROSS2 |
| VALUE, NUMBERVALUE | WERT, ZAHLENWERT |
| CHAR | ZEICHEN |
Which Excel versions have these functions?
SUBSTITUTE, REPLACE, TRIM, CLEAN, UPPER, LOWER, PROPER and VALUE are available in every version of Excel, including Excel for Mac and Excel for the web. NUMBERVALUE was added in Excel 2013, so every current version has it.
FAQ
How do I remove all spaces from a cell?
TRIM keeps one space between words. To remove every space, for example in an IBAN or a serial number, use =SUBSTITUTE(A2," ","").
How do I replace commas with periods in Excel?
Use =SUBSTITUTE(A2,",","."). If the result should be a number, wrap it in VALUE: =VALUE(SUBSTITUTE(A2,",",".")). For a one-time fix, Ctrl+H works too.
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE finds a given piece of text wherever it is. REPLACE works by position: =REPLACE(A2,1,3,"NEW") overwrites the first three characters. For cleaning data, SUBSTITUTE is almost always the one you want.
How do I remove line breaks inside a cell?
A line break is character 10. =SUBSTITUTE(A2,CHAR(10)," ") turns it into a space. CLEAN removes it as well but does not insert a space.
For splitting and joining text, see the text functions lesson.