SUBSTITUTE (more than one in Excel ?)

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" <[email protected]> wrote in message news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> Cheers Peo ! - That works !
>
> Kind Regards
>
> Andrew
>
>
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > =SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA","British Airports
> > Authority")
> >
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Andy100" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> > > news:[email protected]...
> > >> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> > 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" <[email protected]> wrote in message
news:[email protected]...
> =SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA","British Airports
> Authority")
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Andy100" <[email protected]> wrote in message
> news:[email protected]...
> >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
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> > 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>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
> news:[email protected]...
>> 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" <[email protected]> wrote in message
> news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
> 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
>
>

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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>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
> news:[email protected]...
>> 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" <[email protected]> wrote in message
> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
>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
> news:[email protected]...
>> 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" <[email protected]> wrote in message
> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
> =SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA","British Airports
> Authority")
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "Andy100" <[email protected]> wrote in message
> news:[email protected]...
> >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
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> Cheers Peo ! - That works !
>
> Kind Regards
>
> Andrew
>
>
>
>
> "Peo Sjoblom" <[email protected]> wrote in message
> news:[email protected]...
> > =SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA","British Airports
> > Authority")
> >
> >
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> > (No private emails please)
> >
> >
> > "Andy100" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> > > news:[email protected]...
> > >> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> > 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","SPEEDBIRD "),"EZY","EASYJET
"),"MYT","KESTREL ")

will do the job.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.


"Andy100" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> Cheers Peo ! - That works !
>>
>> Kind Regards
>>
>> Andrew
>>
>>
>>
>>
>> "Peo Sjoblom" <[email protected]> wrote in message
>> news:[email protected]...
>> > =SUBSTITUTE(SUBSTITUTE(A2,"BAW","SPEEDBIRD"),"BAA","British Airports
>> > Authority")
>> >
>> >
>> >
>> > --
>> > Regards,
>> >
>> > Peo Sjoblom
>> >
>> > (No private emails please)
>> >
>> >
>> > "Andy100" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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
>> > > news:[email protected]...
>> > >> 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" <[email protected]> wrote in message
>> > > news:[email protected]...
>> > >> > 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
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >
>> > >
>> >
>>
>>
>
>