|
SUBSTITUTE (more than one in Excel ?)
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 |
| All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com