Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.3 Extra Spaces and Non-printable Characters

By Last updated:

Before

Store ID (raw) LEN
" BLK-PUN-01" 12
"BLK-NSK-01 " 13
"AMN-NGP-01" + non-breaking space 11
"BLK-KOP-01" + line break 11

After

Store ID (clean) LEN
BLK-PUN-01 10
BLK-NSK-01 10
AMN-NGP-01 10
BLK-KOP-01 10

Steps in Excel

  1. Check: =LEN(A2) – more characters than you can see means hidden spaces.
  2. Normal spaces: =TRIM(A2).
  3. Line breaks and control characters: =CLEAN(A2).
  4. Non-breaking spaces from web/PDF copies: =SUBSTITUTE(A2,CHAR(160)," ").
  5. All together: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) › copy down › Paste Special › Values over the original.
  6. Find & Replace alternative for CHAR(160): Ctrl + H › in Find what hold Alt and type 0160 on the numeric keypad › Replace with a normal space › Replace All.

Ravindra Bagale's Tip

Khup students TRIM lavun "ho gela clean" samajtat, pan LEN parat check karat nahit. Web kiwa PDF madhun aalelya data madhe CHAR(160) asto, jo TRIM la disat nahi. Mhanun cleaning nantar LEN ne ek sample check kara – 10 akshare apekshit asel tar 10 ch aale pahijet.

Practice task

Paste a few store IDs copied from a web page, measure LEN, clean them with the combined formula and prove they now match the Stores master using XLOOKUP.