topic Re: Multiple Find and Replace Formula in Discussions

Multiple Find and Replace Formula

progers2020 — Tue, 22 Oct 2019 18:53:24 GMT

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

Mark_Bailey — Tue, 22 Oct 2019 18:58:50 GMT

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

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

Re: Multiple Find and Replace Formula

ron_horne — Tue, 22 Oct 2019 20:43:28 GMT

@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

progers2020 — Tue, 22 Oct 2019 21:41:22 GMT

This works great. I was originally under the impression that the Substitute function only worked for one character

Re: Multiple Find and Replace Formula

Mark_Bailey — Wed, 23 Oct 2019 13:08:25 GMT

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.