Multiple Substitutes

**JRowe** · 04-07-2010, 08:09 PM

Hi
Hopefully this will be a simple one for you Guru's to solve as i'm getting too big of a headache trying to work it out.

I'm trying to use the substitute function to get rid of more than one criteria.
e.g. > or To

As i'm not too experienced with other functions i don't know if there is another function that could do that for me?
Hopefully my example wookbook shows what i would like to achieve.
Many thanks for any comments.

**stanleydgromjr** · 04-07-2010, 08:33 PM

JRowe,

The formula in cell D1:
=IF(ISERROR(INDIRECT((A1&B1&SUBSTITUTE(C1," to ","")))),"",INDIRECT((A1&B1&SUBSTITUTE(C1," to ",""))))

The formula in cell D2:
=IF(ISERROR(INDIRECT((A1&B1&SUBSTITUTE(C1,">","")))),"",INDIRECT((A1&B1&SUBSTITUTE(C1,">",""))))

**DonkeyOte** · 04-08-2010, 02:56 AM

If you're looking to return the cell contents via a single calculation perhaps:

=INDIRECT(A1&B1&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1," to ",""),">","")," ",""))

**JRowe** · 04-08-2010, 05:49 AM

Stan
Thank You for your reply, but not quite what i was looking for.

DonkeyOte
Thank You for your reply, that worked perfectly.
Now gotta work out how to intergrate that into my more complicated worksheet haha

Thanks for all your comments.