Excel formula: check if a cell contains part of text.
Solved
plunderer007
Posted messages
82
Status
Membre
-
francois -
francois -
Hello,
I manage relatively well with formulas in Excel, but I'm completely stuck!
I have a column C with different texts (strings). In my column L, I would like to set a condition to check if the text in cell C5 includes a specific string. If the cell contains the string, it should return a defined text, and if the cell does not include the string, it should return nothing.
So I have in my cell L5 the following formula: =IF(SEARCH("*ASA*",C5),"Smartnet","")
I would like that if cell C5 contains the characters "ASA", cell L5 returns the text "Smartnet", and if cell C5 does not contain the characters "ASA", cell L5 returns nothing.
My problem is that it works when the condition is true. However, when the condition is false (if cell C5 does not contain the characters "ASA"), cell L5 does not return nothing but gives the #VALUE! error.
Can someone help me find a solution so that I no longer get that error but nothing shows up instead?
Thank you very much in advance for your help.
*!* IMPORTANT NOTICE *!* -> Google is your friend, think about it before posting!
-> Stop SMS messages! -> Please close your threads once resolved!Configuration: Windows XP, Office Excel 2007
I manage relatively well with formulas in Excel, but I'm completely stuck!
I have a column C with different texts (strings). In my column L, I would like to set a condition to check if the text in cell C5 includes a specific string. If the cell contains the string, it should return a defined text, and if the cell does not include the string, it should return nothing.
So I have in my cell L5 the following formula: =IF(SEARCH("*ASA*",C5),"Smartnet","")
I would like that if cell C5 contains the characters "ASA", cell L5 returns the text "Smartnet", and if cell C5 does not contain the characters "ASA", cell L5 returns nothing.
My problem is that it works when the condition is true. However, when the condition is false (if cell C5 does not contain the characters "ASA"), cell L5 does not return nothing but gives the #VALUE! error.
Can someone help me find a solution so that I no longer get that error but nothing shows up instead?
Thank you very much in advance for your help.
*!* IMPORTANT NOTICE *!* -> Google is your friend, think about it before posting!
-> Stop SMS messages! -> Please close your threads once resolved!Configuration: Windows XP, Office Excel 2007
2 answers
Good evening Gilou,
I tried your solution and I just had to swap the true/false values and it works:
And it works perfectly!!!!!! :) :) :) :) :) :) :) :) :) :)
Thank you very much, very much, very much.
*!* IMPORTANT NOTICE *!* -> Google is your friend, keep this in mind before posting!
-> Stop SMS messages! -> Please close your topics once resolved!
I tried your solution and I just had to swap the true/false values and it works:
=IF(ISERROR(SEARCH("*ASA*";C5));"";"smart") And it works perfectly!!!!!! :) :) :) :) :) :) :) :) :) :)
Thank you very much, very much, very much.
*!* IMPORTANT NOTICE *!* -> Google is your friend, keep this in mind before posting!
-> Stop SMS messages! -> Please close your topics once resolved!
it's not often but it happens to me
have a good evening
--
*!* IMPORTANT NOTICE *!* -> Google is your friend, think about it before posting!
-> No more SMS messages! -> Please close your threads once resolved!