formulastreak

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)).

Workshop sign-ups · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = Mara Okafor
ABCDE
1NameEmailMember codeCityClean name
2 mara okafor mara.okafor@example.comGB-2041LondonMara Okafor
3JONAS LINDjonas.lind@example.orgSE-1187MalmöJonas Lind
4priya ramanpriya.raman@example.netIN-3302PunePriya Raman
5Tomás Ruiz tomas.ruiz@example.comES-0419SevilleTomás Ruiz
6elif demirelif.demir@example.orgTR-2750IzmirElif Demir
7 Kenji MORIkenji.mori@example.netJP-1093OsakaKenji Mori
8sofia rossisofia.rossi@example.comIT-4468TurinSofia Rossi
9Leo Brandtleo.brandt@example.orgDE-3321LeipzigLeo 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.

CRM export · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = London
ABCDEF
1CustomerPhoneOrderAmountCityCity
2Okafor, Mara+44 20 7946 0321ORD-2026-0412$1,250.00London London
3Lind, Jonas+46 40 123 456ORD-2026-0413$89.90MalmöMalmö
4Raman, Priya+91 20 5550 1234ORD-2026-0419$2,040.00 PunePune
5Ruiz, Tomás+34 954 000 111ORD-2026-0420$15.00Seville Seville
6Demir, Elif+90 232 555 0199ORD-2026-0425$310.50IzmirIzmir
7Mori, Kenji+81 6 5550 7788ORD-2026-0431$799.00Osaka  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.

CRM export · edit F2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultF2 = Mara Okafor
ABCDEF
1CustomerPhoneOrderAmountCityFull name
2Okafor, Mara+44 20 7946 0321ORD-2026-0412$1,250.00London Mara Okafor
3Lind, Jonas+46 40 123 456ORD-2026-0413$89.90MalmöJonas Lind
4Raman, Priya+91 20 5550 1234ORD-2026-0419$2,040.00 PunePriya Raman
5Ruiz, Tomás+34 954 000 111ORD-2026-0420$15.00Seville Tomás Ruiz
6Demir, Elif+90 232 555 0199ORD-2026-0425$310.50IzmirElif Demir
7Mori, Kenji+81 6 5550 7788ORD-2026-0431$799.00Osaka  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.

CRM export · edit G1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultG1 = $4,504.40
ABCDEFG
1CustomerPhoneOrderAmountCityTotal$4,504.40
2Okafor, Mara+44 20 7946 0321ORD-2026-0412$1,250.00London 
3Lind, Jonas+46 40 123 456ORD-2026-0413$89.90Malmö
4Raman, Priya+91 20 5550 1234ORD-2026-0419$2,040.00 Pune
5Ruiz, Tomás+34 954 000 111ORD-2026-0420$15.00Seville
6Demir, Elif+90 232 555 0199ORD-2026-0425$310.50Izmir
7Mori, Kenji+81 6 5550 7788ORD-2026-0431$799.00Osaka  

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
Select F2 and type = to start its formula (in the cell or the formula bar)
ABCDEF
1CustomerPhoneOrderAmountCityOrder no.
2Okafor, Mara+44 20 7946 0321ORD-2026-0412$1,250.00London 
3Lind, Jonas+46 40 123 456ORD-2026-0413$89.90Malmö
4Raman, Priya+91 20 5550 1234ORD-2026-0419$2,040.00 Pune
5Ruiz, Tomás+34 954 000 111ORD-2026-0420$15.00Seville
6Demir, Elif+90 232 555 0199ORD-2026-0425$310.50Izmir
7Mori, Kenji+81 6 5550 7788ORD-2026-0431$799.00Osaka  

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.

Practise it now