Clean up messy text
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
Nest the cleaners: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) removes non-breaking spaces, line breaks and extra spaces; PROPER fixes capitals; LEFT/MID with FIND split text; VALUE turns text like "$1,250.00" into a number.
Exported data is rarely clean. A handful of text functions fix almost all of it, and nesting them handles the rest. Every example below is a live sheet.
Spaces and capitals: TRIM and PROPER
TRIM removes leading, trailing and doubled spaces; PROPER capitalises each word. Nest them: PROPER(TRIM(A2)).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Member code | City | Clean name | |
| 2 | mara okafor | mara.okafor@example.com | GB-2041 | London | Mara Okafor |
| 3 | JONAS LIND | jonas.lind@example.org | SE-1187 | Malmö | Jonas Lind |
| 4 | priya raman | priya.raman@example.net | IN-3302 | Pune | Priya Raman |
| 5 | Tomás Ruiz | tomas.ruiz@example.com | ES-0419 | Seville | Tomás Ruiz |
| 6 | elif demir | elif.demir@example.org | TR-2750 | Izmir | Elif Demir |
| 7 | Kenji MORI | kenji.mori@example.net | JP-1093 | Osaka | Kenji Mori |
| 8 | sofia rossi | sofia.rossi@example.com | IT-4468 | Turin | Sofia Rossi |
| 9 | Leo Brandt | leo.brandt@example.org | DE-3321 | Leipzig | Leo Brandt |
Invisible characters: SUBSTITUTE and CLEAN
Text copied from the web often contains non-breaking spaces, CHAR(160), which TRIM ignores. Replace them first, and remove line breaks with CLEAN.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Phone | Order | Amount | City | City |
| 2 | Okafor, Mara | +44 20 7946 0321 | ORD-2026-0412 | $1,250.00 | London | London |
| 3 | Lind, Jonas | +46 40 123 456 | ORD-2026-0413 | $89.90 | Malmö | Malmö |
| 4 | Raman, Priya | +91 20 5550 1234 | ORD-2026-0419 | $2,040.00 | Pune | Pune |
| 5 | Ruiz, Tomás | +34 954 000 111 | ORD-2026-0420 | $15.00 | Seville | Seville |
| 6 | Demir, Elif | +90 232 555 0199 | ORD-2026-0425 | $310.50 | Izmir | Izmir |
| 7 | Mori, Kenji | +81 6 5550 7788 | ORD-2026-0431 | $799.00 | Osaka | Osaka |
Split “Last, First”
Find the separator, then take the text on either side: LEFT(A2, FIND(",", A2) - 1) and MID(A2, FIND(",", A2) + 2, LEN(A2)). RIGHT(C2, 4) takes the last four characters. Excel 2024 and Microsoft 365 also have TEXTBEFORE(A2, ",") and TEXTAFTER(A2, ", "); Google Sheets doesn't.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Phone | Order | Amount | City | Full name |
| 2 | Okafor, Mara | +44 20 7946 0321 | ORD-2026-0412 | $1,250.00 | London | Mara Okafor |
| 3 | Lind, Jonas | +46 40 123 456 | ORD-2026-0413 | $89.90 | Malmö | Jonas Lind |
| 4 | Raman, Priya | +91 20 5550 1234 | ORD-2026-0419 | $2,040.00 | Pune | Priya Raman |
| 5 | Ruiz, Tomás | +34 954 000 111 | ORD-2026-0420 | $15.00 | Seville | Tomás Ruiz |
| 6 | Demir, Elif | +90 232 555 0199 | ORD-2026-0425 | $310.50 | Izmir | Elif Demir |
| 7 | Mori, Kenji | +81 6 5550 7788 | ORD-2026-0431 | $799.00 | Osaka | Kenji Mori |
Numbers stored as text
Imported amounts like “$1,250.00” are text: SUM and COUNT skip them. VALUE converts them; SUMPRODUCT(VALUE(range)) totals them in one go. VALUE follows your locale's separators; in Excel, NUMBERVALUE(text, ",", ".") reads numbers written the European way.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Customer | Phone | Order | Amount | City | Total | $4,504.40 |
| 2 | Okafor, Mara | +44 20 7946 0321 | ORD-2026-0412 | $1,250.00 | London | ||
| 3 | Lind, Jonas | +46 40 123 456 | ORD-2026-0413 | $89.90 | Malmö | ||
| 4 | Raman, Priya | +91 20 5550 1234 | ORD-2026-0419 | $2,040.00 | Pune | ||
| 5 | Ruiz, Tomás | +34 954 000 111 | ORD-2026-0420 | $15.00 | Seville | ||
| 6 | Demir, Elif | +90 232 555 0199 | ORD-2026-0425 | $310.50 | Izmir | ||
| 7 | Mori, Kenji | +81 6 5550 7788 | ORD-2026-0431 | $799.00 | Osaka |
Check yourself
One real exercise from the “Cleaning text at scale” unit. Checked on our server; no account needed.Drop the "ORD-" prefix from each order code (C2), in F2.
- Use SUBSTITUTE
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer | Phone | Order | Amount | City | Order no. |
| 2 | Okafor, Mara | +44 20 7946 0321 | ORD-2026-0412 | $1,250.00 | London | |
| 3 | Lind, Jonas | +46 40 123 456 | ORD-2026-0413 | $89.90 | Malmö | |
| 4 | Raman, Priya | +91 20 5550 1234 | ORD-2026-0419 | $2,040.00 | Pune | |
| 5 | Ruiz, Tomás | +34 954 000 111 | ORD-2026-0420 | $15.00 | Seville | |
| 6 | Demir, Elif | +90 232 555 0199 | ORD-2026-0425 | $310.50 | Izmir | |
| 7 | Mori, Kenji | +81 6 5550 7788 | ORD-2026-0431 | $799.00 | Osaka |
Common mistakes
Expecting TRIM to remove non-breaking spaces (it doesn't).
Overwriting the original data with the cleaned version before checking it. Clean into a new column first.
Using FIND when the case may differ:
SEARCH("pot", D2)ignores case (and allows wildcards), FIND doesn't.
Questions
How do I keep the cleaned values and drop the formulas?+
Copy the cleaned column and Paste Special → Values (Excel) or Paste values only (Google Sheets).
Is there a function that splits text into columns?+
Google Sheets has SPLIT; Excel 2024 and Microsoft 365 have TEXTSPLIT (Google Sheets doesn't). Both spill the parts into neighbouring cells.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.