If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update to formula
Is it possible for a formula to look in cell and check if it contains
specific text? I was provided the following formula, below, but it only checks to see if cell value contains only those values but not if it includes it. For example: cell value - Test0408AU ="Exist" =IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot find") |
#2
|
|||
|
|||
Update to formula
Search accepts wildcards, so if you want to search for contains "Q3", use
"*Q3*". Ditto for the others in the list. Regards, Fred "Jen_T" wrote in message ... Is it possible for a formula to look in cell and check if it contains specific text? I was provided the following formula, below, but it only checks to see if cell value contains only those values but not if it includes it. For example: cell value - Test0408AU ="Exist" =IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot find") |
#3
|
|||
|
|||
Update to formula
That formula works for me. I would note, though, that you do not need 0408AU
in the list... any time 0408 is in the list, a match will be found for that number which appears later on in your list... that is, the 0408 text can be surrounded by any characters and the formula will report "Exist". -- Rick (MVP - Excel) "Jen_T" wrote in message ... Is it possible for a formula to look in cell and check if it contains specific text? I was provided the following formula, below, but it only checks to see if cell value contains only those values but not if it includes it. For example: cell value - Test0408AU ="Exist" =IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot find") |
#4
|
|||
|
|||
Update to formula
"Jen_T" wrote:
=IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU", "0408SL","0408","0109","0209","0309","0409"},A 1)), "Exist","Cannot find") [....] it only checks to see if cell value contains only those values but not if it includes it. That is incorrect. It works just fine with your test data, Test0408AU. Are you copy-and-pasting the formula from the Formula Bar? I wonder if you have some subtle error in the actual formula, which you unconsciously corrected if you retyped the formula in your inquiry. Note that "0408AU" and "0408SL" are redundant, since you have "0408". But that should not be causing any problems. ----- original message ----- "Jen_T" wrote: Is it possible for a formula to look in cell and check if it contains specific text? I was provided the following formula, below, but it only checks to see if cell value contains only those values but not if it includes it. For example: cell value - Test0408AU ="Exist" =IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot find") |
#5
|
|||
|
|||
Update to formula
In that application no wildcards are needed.
You would use wildcards if: Search for Q?3 Where ? is any single character Search for Q*3 Where * is any number of characters or no characters followed by a 3. A2 = Q1123 =COUNT(SEARCH("Q?3",A2)) = 0 =COUNT(SEARCH("Q*3",A2)) = 1 A2 = Q13 =COUNT(SEARCH("Q?3",A2)) = 1 =COUNT(SEARCH("Q*3",A2)) = 1 -- Biff Microsoft Excel MVP "Fred Smith" wrote in message ... Search accepts wildcards, so if you want to search for contains "Q3", use "*Q3*". Ditto for the others in the list. Regards, Fred "Jen_T" wrote in message ... Is it possible for a formula to look in cell and check if it contains specific text? I was provided the following formula, below, but it only checks to see if cell value contains only those values but not if it includes it. For example: cell value - Test0408AU ="Exist" =IF(COUNT(SEARCH({"Q3","0308","Q#3","0408AU","0408 SL","0408","0109","0209","0309","0409"},A1)),"Exis t","Cannot find") |
Thread Tools | |
Display Modes | |
|
|