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 |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on substitute replace trim clean and case functions.
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: " greenvale cow milk 500 ML". =PROPER(TRIM(A2)) → Greenvale Cow Milk 500 Ml. Then fix the unit: =SUBSTITUTE(PROPER(TRIM(A2)),"Ml","ml") → Greenvale 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.
Loading practice tasks...
- Fix the name case (Easy): The raw name "sHRADDHA bAGALE" is in the wrong case. Fix it so it looks like Shraddha Bagale.
- Remove extra spaces (Easy): The raw text " Baner Pune " has extra spaces. Remove them.
- Insurance: fix the holder name (Easy): On the Insurance sheet, the holder name "mARIA gARCIA" is in the wrong case. Fix it so it looks like Maria Garcia.
- Insurance: small letters (Easy): On the Insurance sheet, make the plan code "HEALTH-PLAN-GOLD" all small letters.
- Hide the phone number (Medium): Hide the first 6 digits of the phone number 9876543210 with XXXXXX.
- Change a word (Medium): In the address "Aurangabad Road, Sambhaji Nagar", change "Aurangabad" to "Sambhaji Nagar".
- Insurance: clean policy number (Medium): On the Insurance sheet, the policy ID " pol-77812 " is messy. Make it look like POL-77812.
- Clean product name (Hard): The product name " greenvale cow milk 500 ML" is messy. Make it look like Greenvale Cow Milk 500 Ml (no extra spaces, proper case).
- Upper case code (Hard): Make the raw name "sHRADDHA bAGALE" all capital letters.
- Insurance: change the dash (Hard): On the Insurance sheet, in "Motor Insurance - Third Party", change " - " to ": ".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
