5.3 Extra Spaces and Non-printable Characters
TRIM extra spaces with example — in 30 seconds
What: TRIM removes spaces at the start and end, and turns double spaces inside the text into single spaces.
Why: "Pune " with a hidden space is not equal to "Pune". COUNTIF and lookups quietly miss those rows.
Tiny example: " Pune " has LEN 7. After =TRIM(A2) it is "Pune" with LEN 4.
TRIM extra spaces with example — 30 सेकंदात
काय: TRIM सुरुवातीचे आणि शेवटचे spaces काढतो, आणि text मधले double spaces single करतो.
का: लपलेला space असलेलं "Pune " हे "Pune" शी equal नसतं. COUNTIF आणि lookup त्या rows गुपचूप सोडून देतात.
छोटं उदाहरण: " Pune " चा LEN 7 आहे. =TRIM(A2) नंतर ते "Pune" होतं आणि LEN 4.
TRIM extra spaces with example — 30 सेकंड में
क्या: TRIM शुरू और आख़िर के spaces हटाता है, और text के अंदर के double spaces को single कर देता है.
क्यों: छुपे space वाला "Pune " और "Pune" बराबर नहीं होते. COUNTIF और lookup उन rows को चुपचाप छोड़ देते हैं.
छोटा उदाहरण: " Pune " का LEN 7 है. =TRIM(A2) के बाद यह "Pune" बनता है और LEN 4.
First look at the data (before → after):
Expected result — After TRIM
| B: Clean | C: LEN |
|---|---|
| "Pune" | 4 |
| "Nashik Road" | 11 |
| "Kolhapur" | 8 |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Aamir Khan) are made up for this lesson on extra spaces and non printable characters.
Before table and formula as text
Before — Raw city text (spaces shown inside quotes)
| A: Raw | =LEN(A2) |
|---|---|
| " Pune " | 7 |
| "Nashik Road" | 12 |
| " Kolhapur" | 9 |
About this data: Demo table only — not real customer or company data. Built to practise extra spaces and non printable characters.
Formula / click path
B2: =TRIM(A2)
C2: =LEN(B2)
Do it step by step:
- In B2, type
=LEN(A2)first to see the hidden spaces. - In C2, type
=TRIM(A2). - Copy down and compare the lengths.
- Copy column C and use Paste Special › Values over column A.
- If LEN is still too big, the data may have non-breaking spaces. Use
=SUBSTITUTE(A2,CHAR(160)," ")before TRIM.
स्टेप बाय स्टेप करून बघा:
- लपलेले spaces बघायला आधी B2 मध्ये
=LEN(A2)type करा. - C2 मध्ये
=TRIM(A2)type करा. - खाली copy करा आणि lengths जुळवून बघा.
- Column C copy करा आणि column A वर Paste Special › Values करा.
- तरीही LEN जास्त येत असेल तर data मध्ये non-breaking spaces असू शकतात. TRIM आधी
=SUBSTITUTE(A2,CHAR(160)," ")वापरा.
स्टेप बाय स्टेप करके देखें:
- छुपे spaces देखने के लिए पहले B2 में
=LEN(A2)type करें. - C2 में
=TRIM(A2)type करें. - नीचे copy करें और lengths मिलाएँ.
- Column C copy करें और column A पर Paste Special › Values करें.
- फिर भी LEN ज़्यादा आए तो data में non-breaking spaces हो सकते हैं. TRIM से पहले
=SUBSTITUTE(A2,CHAR(160)," ")इस्तेमाल करें.
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 |
About this data: Made-up rows so you can copy the pattern for extra spaces and non printable characters. Currencies may use ₹, £, € or $.
After
| Store ID (clean) | LEN |
|---|---|
| BLK-PUN-01 | 10 |
| BLK-NSK-01 | 10 |
| AMN-NGP-01 | 10 |
| BLK-KOP-01 | 10 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
Steps in Excel
- Check:
=LEN(A2)– more characters than you can see means hidden spaces. - Normal spaces:
=TRIM(A2). - Line breaks and control characters:
=CLEAN(A2). - Non-breaking spaces from web/PDF copies:
=SUBSTITUTE(A2,CHAR(160)," "). - All together:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))› copy down › Paste Special › Values over the original. - 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
Many students apply TRIM and think "it's clean now", but don't check LEN again. Data from the web or a PDF contains CHAR(160), which TRIM doesn't see. So after cleaning, check a sample with LEN – if you expect 10 characters, you should get exactly 10.
Ravindra Bagale's Tip – मराठी
बरेच students TRIM लावून "झालं clean" समजतात, पण LEN परत check करत नाहीत. Web किंवा PDF मधून आलेल्या data मध्ये CHAR(160) असतो, जो TRIM ला दिसत नाही. म्हणून cleaning नंतर LEN ने एक sample check करा – 10 अक्षरं अपेक्षित असतील तर 10 च आली पाहिजेत.
Ravindra Bagale's Tip – हिंदी
बहुत से students TRIM लगाकर "हो गया clean" मान लेते हैं, पर LEN दोबारा check नहीं करते. Web या PDF से आए data में CHAR(160) होता है, जो TRIM को दिखता नहीं. इसलिए cleaning के बाद LEN से एक sample check करो – 10 अक्षर चाहिए तो ठीक 10 ही आने चाहिए.
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.
Loading practice tasks...
- Clean a name (Easy): Clean the name in B2.
- Length of a raw name (Easy): How many characters are in B2?
- Remove a double space (Easy): Clean the name in B3.
- Clean a city (Easy): Clean the city in C7.
- Count extra spaces (Medium): How many extra characters does B5 have? (length minus cleaned length)
- Is it really Carlos Diaz? (Medium): Does the cleaned B3 equal "Carlos Diaz"? (TRUE or FALSE)
- Hidden space in a city (Medium): Clean the city in C4.
- Count hidden characters (Medium): How many characters does CLEAN remove from B6?
- Line break to space (Hard): Make B6 read "Neha Deshpande".
- Full clean (Hard): Clean B4 fully.
- Clean a tracking number (Hard): Clean the AWB in D5.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
