Choose Language Hide Translation Bar
Highlighted
progers2020
Level I

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

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.", ""
);

 

Learn it once, use it forever!

View solution in original post

Highlighted
ron_horne
Super User

Re: Multiple Find and Replace Formula

@markbailey is almost perfect.

but I think that the function is order dependent.

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

View solution in original post

4 REPLIES 4
Highlighted

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.", ""
);

 

Learn it once, use it forever!

View solution in original post

Highlighted
ron_horne
Super User

Re: Multiple Find and Replace Formula

@markbailey is almost perfect.

but I think that the function is order dependent.

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

View solution in original post

Highlighted
progers2020
Level I

Re: Multiple Find and Replace Formula

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

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.

Learn it once, use it forever!
Article Labels

    There are no labels assigned to this post.