cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
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

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

 

View solution in original post

ron_horne
Super User (Alumni)

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

View solution in original post

4 REPLIES 4

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

 

ron_horne
Super User (Alumni)

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

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.