ISNUMBER Help

[SkiliedSin]

Hello,

So the cell contains "MattressSofaCouch-1", I currently have the isnumber function to identify whether it is a mattress, sofa or couch but I need an adjustment to the formula to designate the "-1" as the color black.

Right now when I use the ISNUMBER formula and add the "-1", it pulls Mattress first instead of "black", is there any way to adjust the formula to not pull the first "Mattress" but rather display "Mattress - black"

 

[Aryatect]

Hi, Can you please some sample data and the formula you are using.

 

[SkiliedSin]

Sure,

"MattressSofaCouch", "MattressSofaCouch-1" are some examples and the formula I am using is =IF(ISNUMBER(SEARCH("Mattress",D1)),"Comfy",IF(ISNUMBER(SEARCH("Sofa",D1)),"Hard",IF(ISNUMBER(SEARCH("Couch",D1)),"Soft",IF(ISNUMBER(SEARCH("-1",D1)),"Black","")))

The issue is that the formula sees Mattress first so pulls Comfy instead of Black.

 

[Rick Rothstein]

Sure,

"MattressSofaCouch", "MattressSofaCouch-1" are some examples and the formula I am using is =IF(ISNUMBER(SEARCH("Mattress",D1)),"Comfy",IF(ISNUMBER(SEARCH("Sofa",D1)),"Hard",IF(ISNUMBER(SEARCH("Couch",D1)),"Soft",IF(ISNUMBER(SEARCH("-1",D1)),"Black","")))

The issue is that the formula sees Mattress first so pulls Comfy instead of Black.

Test for the "-1" first in your chain of IF function calls, not last as you have it now. That way, if "-1" is in the text it will be found first and you can return "Black" for it... if "-1" is not in the text, then the next thing searched for would be "Mattress" and so on.

 

[Aryatect]

Hi SkiliedSin, without going into any other changes below should work for you:

=IF(ISNUMBER(SEARCH("Mattress",D1)),"Comfy",IF(ISNUMBER(SEARCH("Sofa",D1)),"Hard",IF(ISNUMBER(SEARCH("Couch",D1)),"Soft","ERROR")))&" "&IF(ISNUMBER(SEARCH("-1",D1)),"Black","")