3.7 SUBSTITUTE, REPLACE, TRIM, CLEAN and Case Functions

By Ravindra Bagale Last updated: 25 September 2026

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

TRIM lavla tari lookup match hot nahi – asa khup students sangtat. Karan web kiwa PDF madhun aalelya data madhe CHAR(160) asto, jo TRIM kadhat nahi. =LEN(A2) ne length check kara; disnarya aksharanpeksha jast asel tar SUBSTITUTE(…,CHAR(160)," ") vapra.

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.