5.14 Removing Unwanted Characters
Before
| Product (raw) |
|---|
| *Nashik Grapes* 500 g |
| Poha #1 kg |
| Ladi Pav (6 pcs)!! |
| Amul_Butter_100_g |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Aamir Khan) are made up for this lesson on removing unwanted characters.
After
| Product |
|---|
| Nashik Grapes 500 g |
| Poha 1 kg |
| Ladi Pav (6 pcs) |
| Amul Butter 100 g |
About this data: Demo table only — not real customer or company data. Built to practise removing unwanted characters.
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.
Loading practice tasks...
- Remove stars (Easy): Remove all * from B2.
- Underscore to space (Easy): Change "_" to " " in B3.
- Remove hash signs (Easy): Remove all # from B4.
- Dot to space (Easy): Change "." to " " in B6.
- Remove a note (Medium): Clean B5 to "Daniel Moore".
- Remove the $ (Medium): Remove "$" from C3.
- Price as a number (Medium): Change C2 into a number.
- Count symbols (Medium): Count "#" in B4.
- Total price (Hard): Total of C2:C6 as numbers.
- Clean name, proper case (Hard): Clean B3 into "Michael Brown".
- Remove both symbols (Hard): Remove "*" and "#" from B2 in one formula.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
