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


"Andy100" wrote in message ...
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

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



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Andy100" wrote in message
...
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

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



--
Regards,

Peo Sjoblom

(No private emails please)


"Andy100" wrote in message
...
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



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Andy100" wrote in message
...
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

Cheers Peo ! - That works !

Kind Regards

Andrew




"Peo Sjoblom" wrote in message
...
=SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA" ,"British Airports
Authority")



--
Regards,

Peo Sjoblom

(No private emails please)


"Andy100" wrote in message
...
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



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Andy100" wrote in message
...
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

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




"Andy100" wrote in message
...
Cheers Peo ! - That works !

Kind Regards

Andrew




"Peo Sjoblom" wrote in message
...
=SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA" ,"British Airports
Authority")



--
Regards,

Peo Sjoblom

(No private emails please)


"Andy100" wrote in message
...
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



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Andy100" wrote in message
...
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

Hi Andrew

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

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBI RD "),"EZY","EASYJET
"),"MYT","KESTREL ")

will do the job.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.


"Andy100" skrev i en meddelelse
...
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




"Andy100" wrote in message
...
Cheers Peo ! - That works !

Kind Regards

Andrew




"Peo Sjoblom" wrote in message
...
=SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA" ,"British Airports
Authority")



--
Regards,

Peo Sjoblom

(No private emails please)


"Andy100" wrote in message
...
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



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Andy100" wrote in message
...
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