SUBSTITUTE (more than one in Excel ?)

  • Thread starter Thread starter Andy100
  • Start date Start date
A

Andy100

Using the SUBSTITUTE function in Excel 2002, can i have more than one
substitute in a column ??

I have an excel sheet, e.g. Col A i want to change everytime it sees "BAW"
to "British Airways", but also whenever it sees "BAA" i need it to change to
"British Airports Authority". There are many more substitutions i need to
make but i can only get it to work with one at a time !!

Cheers
Andy
 
Andy,

Make up a table of your substitutions (say, in X1:Y100, with codes in column X and full values in
Y), and use a formula like

=VLOOKUP(A1,$X$1:$Y$100,2,False)

HTH,
Bernie
MS Excel MVP
 
I can see what you mean, but in my example it doesn't work because i need it
to replace text within text, not a whole word.

In my data i have e.g. BAW9999, and i want it to change that to
SPEEDBIRD9999, so it's not looking for a whole word, but text within a word
and replacing it.

Thanks for the help and speedy reply !!


Kind Regards
Andrew
 
=SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA","British Airports
Authority")



--
Regards,

Peo Sjoblom

(No private emails please)
 
Sorry to bother you again, but what about 3 or more substitutions ?
Following on from your formula i tried to input more than the two in your
example but got errors ! - i tried:
SUBSTITUTE(SUBSTITUTE((A2,"BAW","SPEEDBIRD "),"EZY","EASYJET
"),"MYT","KESTREL ")

but i kept on getting errors, what pattern will it follow for 3 or more ?

Kind Regards
Andrew
 
Hi Andrew

Each SUBSTITUTE returns a string, which you must
make a new SUBSTITUTE to, so

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD "),"EZY","EASYJET
"),"MYT","KESTREL ")

will do the job.
 
Back
Top