3.7 SUBSTITUTE, REPLACE, TRIM, CLEAN and Case Functions
| Function | Example | Result |
|---|---|---|
SUBSTITUTE(text, old, new, [instance]) |
=SUBSTITUTE("Aurangabad Road","Aurangabad","Sambhaji Nagar") |
Sambhaji Nagar Road |
REPLACE(old_text, start, num_chars, new_text) |
=REPLACE("9876543210",1,6,"XXXXXX") |
XXXXXX3210 |
TRIM(text) |
=TRIM(" Baner Pune ") |
Baner Pune |
CLEAN(text) |
removes non-printable characters (codes 0–31), e.g. line breaks | |
UPPER / LOWER / PROPER |
=PROPER("sHRADDHA bAGALE") |
Shraddha Bagale |
SUBSTITUTE replaces what (by text); REPLACE replaces where (by position). TRIM removes leading/trailing spaces and reduces inside spaces to one – but not the non-breaking space CHAR(160) that comes from web pages. For that:
=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))
Worked example. Product names from a supplier: " gokul cow milk 500 ML". =PROPER(TRIM(A2)) → Gokul Cow Milk 500 Ml. Then fix the unit: =SUBSTITUTE(PROPER(TRIM(A2)),"Ml","ml") → Gokul Cow Milk 500 ml.
Ravindra Bagale's Tip
"Even after TRIM, the lookup doesn't match" – many students tell me this. The reason is that data coming from the web or a PDF contains CHAR(160), which TRIM does not remove. Check the length with =LEN(A2); if it is more than the visible characters, use SUBSTITUTE(…,CHAR(160)," ").
Ravindra Bagale's Tip – मराठी
TRIM लावलं तरी lookup match होत नाही – असं बरेच students सांगतात. कारण web किंवा PDF मधून आलेल्या data मध्ये CHAR(160) असतो, जो TRIM काढत नाही. =LEN(A2) ने length check करा; दिसणाऱ्या अक्षरांपेक्षा जास्त असेल तर SUBSTITUTE(…,CHAR(160)," ") वापरा.
Ravindra Bagale's Tip – हिंदी
"TRIM लगाने के बाद भी lookup match नहीं होता" – यह बहुत से students कहते हैं. वजह यह है कि web या PDF से आए data में CHAR(160) होता है, जिसे TRIM नहीं हटाता. =LEN(A2) से length check करो; अगर दिखने वाले अक्षरों से ज़्यादा हो तो SUBSTITUTE(…,CHAR(160)," ") इस्तेमाल करो.
Practice task
Clean a column of messy area names (extra spaces, wrong case, one line break) into proper case. Mask customer phone numbers so only the last 4 digits show.