Substitute Formula

A

AmyD

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?
 
R

Ron Coderre

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
 
S

Stefi

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

Regards,
Stefi

„AmyD†ezt írta:
 
Top