5.14 Removing Unwanted Characters
Before
| Product (raw) |
|---|
| *Nashik Grapes* 500 g |
| Poha #1 kg |
| Ladi Pav (6 pcs)!! |
| Amul_Butter_100_g |
After
| Product |
|---|
| Nashik Grapes 500 g |
| Poha 1 kg |
| Ladi Pav (6 pcs) |
| Amul Butter 100 g |
Steps in Excel
- Several characters with nested SUBSTITUTE:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"*",""),"#",""),"!",""),"_"," ")). - Find & Replace for one character at a time. To find a literal
*or?, type~*or~?(the tilde escapes the wildcard). -
Microsoft 365 – remove a list of characters with REDUCE/LAMBDA (advanced, optional):
=TRIM(REDUCE(A2,{"*","#","!"},LAMBDA(t,c,SUBSTITUTE(t,c,""))))
Ravindra Bagale's Tip
When searching for * in Find & Replace, many students end up with a whole empty column – because * means "anything" (a wildcard). For a literal star, write ~*. Before Replace All, use Find All to see how many cells are found, then replace.
Ravindra Bagale's Tip – मराठी
Find & Replace मध्ये * शोधायला गेल्यावर बऱ्याच students चा पूर्ण column रिकामा होतो – कारण * म्हणजे "काहीही" (wildcard). खऱ्या star साठी ~* लिहा. Replace All करण्याआधी Find All ने किती cells सापडतात ते बघा, मग replace करा.
Ravindra Bagale's Tip – हिंदी
Find & Replace में * ढूँढने पर बहुत से students का पूरा column खाली हो जाता है – क्योंकि * का मतलब "कुछ भी" (wildcard). असली star के लिए ~* लिखो. Replace All से पहले Find All से देखो कि कितनी cells मिलती हैं, फिर replace करो.
Practice task
Clean 10 product names containing *, #, _, !! and double spaces. Do it once with Find & Replace (using ~*) and once with a SUBSTITUTE formula.