Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.14 Removing Unwanted Characters

By Last updated:

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

  1. Several characters with nested SUBSTITUTE: =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"*",""),"#",""),"!",""),"_"," ")).
  2. Find & Replace for one character at a time. To find a literal * or ?, type ~* or ~? (the tilde escapes the wildcard).

  3. 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

Find & Replace madhe * shodhayla gelyavar khup students cha poora column rikama hoto – karan * mhanje "kahihi" (wildcard). Literal star sathi ~* liha. Replace All karaychya aadhi Find All ne kiti cells sapadtat te bagha, mag replace kara.

Practice task

Clean 10 product names containing *, #, _, !! and double spaces. Do it once with Find & Replace (using ~*) and once with a SUBSTITUTE formula.