I need to create a formula which will allow me to substitute/delete text that
appear in numbers. ie HKD-2,500.00 or YEN-2,500.00. I have used the
substitute formula but it will only delete one of the text's specified but I
want it to pick one or the other
ie HKD/YEN. Can the substitute formula be nested if so how?

Could you, instead, just return the number after the dash?:
A1: HKD-2,500.00
B1: =--MID(A1,FIND("-",A1)+1,255)
(returns 2500)


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"AmyD" wrote:

I need to create a formula which will allow me to substitute/delete text that
appear in numbers. ie HKD-2,500.00 or YEN-2,500.00. I have used the
substitute formula but it will only delete one of the text's specified but I
want it to pick one or the other
ie HKD/YEN. Can the substitute formula be nested if so how?

=SUBSTITUTE(SUBSTITUTE(A1,"YEN",""),"HKD","")
if you mean this!

Regards,
Stefi

€žAmyD€� ezt Ã*rta:

I need to create a formula which will allow me to substitute/delete text that
appear in numbers. ie HKD-2,500.00 or YEN-2,500.00. I have used the
substitute formula but it will only delete one of the text's specified but I
want it to pick one or the other
ie HKD/YEN. Can the substitute formula be nested if so how?