|
How to change cell value if cell contain specified word
Dear All,
I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
You can't change a cell value by a formula. Yu can use a helper column with a
formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
Dear Stefi,
Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
Do you mean that cells containing Ignition off should change to mere Ignition
off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi €˛Hassan€¯ ezt Ć*rta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
Yes Stefi, Exactly same.
"Stefi" wrote: Do you mean that cells containing Ignition off should change to mere Ignition off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi €˛Hassan€¯ ezt Ć*rta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
Does this work for you:
=IF(COUNTIF(A1,"*ignition off")0,"Ignition off",A1) ? Copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Hassan" wrote in message ... Yes Stefi, Exactly same. "Stefi" wrote: Do you mean that cells containing Ignition off should change to mere Ignition off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi €˛Hassan€¯ ezt Ć*rta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
Thanks for reply Ragdyer, but following is not working.
"Ragdyer" wrote: Does this work for you: =IF(COUNTIF(A1,"*ignition off")0,"Ignition off",A1) ? Copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Hassan" wrote in message ... Yes Stefi, Exactly same. "Stefi" wrote: Do you mean that cells containing Ignition off should change to mere Ignition off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi €˛Hassan€¯ ezt Ć*rta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
Hassan
I note some inconsistency with your requirements. Your example shows you want result to be ignition off You then told Stefi you want just ignition Whatever the case, RD's formula will work on your example data but you may want to alter it slightly for the second case. =IF(COUNTIF(A1,"*ignition off")0,"ignition",A1) Gord Dibben MS Excel MVP On Sun, 27 Jul 2008 01:08:01 -0700, Hassan wrote: Thanks for reply Ragdyer, but following is not working. "Ragdyer" wrote: Does this work for you: =IF(COUNTIF(A1,"*ignition off")0,"Ignition off",A1) ? Copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Hassan" wrote in message ... Yes Stefi, Exactly same. "Stefi" wrote: Do you mean that cells containing Ignition off should change to mere Ignition off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi „Hassan” ezt ķrta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi „Hassan” ezt ķrta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
To save everyone the necessity of continued *guessing* at exactly what
you're looking for, would you at least extend us all the courtesy of informing us as to what didn't work? What did you get that you didn't want, OR what didn't you get that you wanted! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Hassan" wrote in message ... Thanks for reply Ragdyer, but following is not working. "Ragdyer" wrote: Does this work for you: =IF(COUNTIF(A1,"*ignition off")0,"Ignition off",A1) ? Copy down as needed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Hassan" wrote in message ... Yes Stefi, Exactly same. "Stefi" wrote: Do you mean that cells containing Ignition off should change to mere Ignition off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi €˛Hassan€¯ ezt Ć*rta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
Thanks Ragdyer,
Can I use two statuses instead of one, e.g. Ignition off and Startup as well. Thanks Hassan "Ragdyer" wrote: To save everyone the necessity of continued *guessing* at exactly what you're looking for, would you at least extend us all the courtesy of informing us as to what didn't work? What did you get that you didn't want, OR what didn't you get that you wanted! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Hassan" wrote in message ... Thanks for reply Ragdyer, but following is not working. "Ragdyer" wrote: Does this work for you: =IF(COUNTIF(A1,"*ignition off")0,"Ignition off",A1) ? Copy down as needed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Hassan" wrote in message ... Yes Stefi, Exactly same. "Stefi" wrote: Do you mean that cells containing Ignition off should change to mere Ignition off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi €˛Hassan€¯ ezt Ć*rta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi €˛Hassan€¯ ezt Ć*rta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
How to change cell value if cell contain specified word
This assumes that "ignition off" and "startup" are *not* in the same cell:
=IF(COUNTIF(A1,"*ignition off"),"Ignition off",IF(COUNTIF(A1,"*startup*"),"startup",A1)) Copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "TFMR" wrote in message ... Thanks Ragdyer, Can I use two statuses instead of one, e.g. Ignition off and Startup as well. Thanks Hassan "Ragdyer" wrote: To save everyone the necessity of continued *guessing* at exactly what you're looking for, would you at least extend us all the courtesy of informing us as to what didn't work? What did you get that you didn't want, OR what didn't you get that you wanted! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Hassan" wrote in message ... Thanks for reply Ragdyer, but following is not working. "Ragdyer" wrote: Does this work for you: =IF(COUNTIF(A1,"*ignition off")0,"Ignition off",A1) ? Copy down as needed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Hassan" wrote in message ... Yes Stefi, Exactly same. "Stefi" wrote: Do you mean that cells containing Ignition off should change to mere Ignition off and all other words should be removed? E.g. Old content new content GPS Ignition off Ignition off Unlock Ignition off Ignition off Health check Ignition off Ignition off GPS Unlock Ignition off Ignition off Stefi "Hassan" ezt ķrta: Dear Stefi, Thanks for reply, following formula only remove Ignition off and other word is still there, I want remove other word with Ignition off Thanks "Stefi" wrote: You can't change a cell value by a formula. Yu can use a helper column with a formula in it returning the required result: =TRIM(SUBSTITUTE(A1,"Ignition off","")) Regards, Stefi "Hassan" ezt ķrta: Dear All, I am looking for a formula that will change all the cells value if that contains the specified word. e.g. GPS Ignition off Unlock Ignition off Health check Ignition off GPS Unlock Ignition off if a cell contains Ignition off then cell value should change with Ignition off and remove the other status with Ignition off. Thanks |
| All times are GMT +1. The time now is 10:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com