If text is within a string then return a certain number...not working

[Warren Keogh]

Hi

I have a sample file attached. I want to a cell containing text. If the text is there then it returns an account code number, (there could be multiple searches). This is the formula I have used and it only works for the first text search ("CAR") what am I doing wrong??

=IF(SEARCH("*"&"CAR"&"*",A2),208,IF(SEARCH("*"&"BIKE"&"*",A2),206))

Thanks

WK

 

[KenU]

@Warren Keogh,

When the search function does not find the specified text, it produces an error and not a true/false. To correct your formula, you simply need to handle the error. You may try either of the options below.

=IF(IFERROR(SEARCH("*"&"CAR"&"*",A2),),208,IF(IFERROR(SEARCH("*"&"BIKE"&"*",A2),),206))

or

=IF(NOT(ISERROR(SEARCH("*"&"CAR"&"*",A2))),208,IF(NOT(ISERROR(SEARCH("*"&"BIKE"&"*",A2))),206))

Hope that helps.

Regards,
Ken

 

[John()]

Here is another way
=IF(ISNUMBER(FIND("CAR",$A$2:$A$8)),208,IF(ISNUMBER(FIND("BIKE",RNG)),206,""))

 

[John()]

Here is another way
=IF(ISNUMBER(FIND("CAR",$A$2:$A$8)),208,IF(ISNUMBER(FIND("BIKE",RNG)),206,""))

if you need more things to check for you can add to this formula and probably the above ones as well ... but be careful that you dont have something to check for that contains two of the criteria eg CAR HIT BIKE as it will only pick the first match

 

[Warren Keogh]

@Warren Keogh,

When the search function does not find the specified text, it produces an error and not a true/false. To correct your formula, you simply need to handle the error. You may try either of the options below.

=IF(IFERROR(SEARCH("*"&"CAR"&"*",A2),),208,IF(IFERROR(SEARCH("*"&"BIKE"&"*",A2),),206))

or

=IF(NOT(ISERROR(SEARCH("*"&"CAR"&"*",A2))),208,IF(NOT(ISERROR(SEARCH("*"&"BIKE"&"*",A2))),206))

Hope that helps.

Regards,
Ken

Thanks Ken very useful.

 

[Warren Keogh]

Here is another way
=IF(ISNUMBER(FIND("CAR",$A$2:$A$8)),208,IF(ISNUMBER(FIND("BIKE",RNG)),206,""))

if you need more things to check for you can add to this formula and probably the above ones as well ... but be careful that you dont have something to check for that contains two of the criteria eg CAR HIT BIKE as it will only pick the first match

Thanks John very helpful.