substitute several words in one column

[miaomay]

Hi all,

In the raw file, there have several different words I need to clean up first so I use formula SUBSTITUTE to do it. For example, I need to substitute %20, http://, and WER: to be blank. I created three columns, one for Substitute[ColumnA, "%20",""], another column Substitute[ColumnB, "http://",""] and the other Substitute[ColumnC, "WER:",""].

Does anyone know how to combine three factors into one column if one or more of them exists, replace them to blank?

Thank you!

 

[schielrn]

Maybe you are looking for something like:

=substitute(substitute(substitute(a1,"%20",""),"http://",""),"WER:","")

Change a1 to the right cell reference.

Hope that helps.

 

[miaomay]

Thank you so much! It works!!

Maybe you are looking for something like:

=substitute(substitute(substitute(a1,"%20",""),"http://",""),"WER:","")

Change a1 to the right cell reference.

Hope that helps.