230121_en-US

liliconcom

Multiple Find and Replace Formula

I am trying to take a company name like ABP MEAT DISTRIBUTORS, INC. and do some Find and Replaces.  I would like to Find ".", ",", and "INC" and replace with blanks.   The substitute formula only seem to work for 1 character.  How can I do multiple Find and Replaces?

Re: Multiple Find and Replace Formula

You might not be aware but in addition to replacing more than one character, it replaces all instances in one call. It also works with lists and expressions.

Re: Multiple Find and Replace FormulaThis works great. I was originally under the impression that the Substitute function only worked for one characterRe: Multiple Find and Replace Formula

@Mark_Bailey is almost perfect.

but I think that the function is order dependent.

Substitute(
 "ABP MEAT DISTRIBUTORS, INC.",
 ",", "",
 "INC.", "",
 ".", ""
);

Re: Multiple Find and Replace Formula

I used literal values to illustrate how Substitute() works. You could use variables or lists instead.

 

Substitute(
 "ABP MEAT DISTRIBUTORS, INC.",
 ".", "",
 ",", "",
 "INC.", ""
);