If a cell contains a specific text than return a value

[dcdeez]

Everywhere I search I see this formula for looking in a column and if a row contains a specific value then to return x, if not return y.
=IF(ISNUMBER(SEARCH("text", A1)),"X","Y")

But when I use this in Excel 2010 I get an error. I modify it to look like this... =IF(ISNUMBER(SEARCH("text", A1,,0)),"X","Y")

It then just returns "X" in every row for my new column.

What am I doing wrong?

Thanks,

[dilipandey]

HI dcdeez,


You need to search in a cell or in entire column ?
If you need to search in a cell, then your above formula (without modification) is working perfectly. If a1 has "text" then formula will output result as X.



Regards,
DILIPandey
<click on below * if this helps>

[dcdeez]

Ah I'm sorry. I need it do the entire [A] column. but if I used [A] instead of A1 it does the same thing.

=IF(ISNUMBER(SEARCH("text", [A],,0)),"X","Y")

[XOR LX]

So you want to return an "X" if any of the entries in that column contains "text"? You can adapt it slightly to this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER):

=IF(SUM(--ISNUMBER(SEARCH("text",A1:A1000)))>0,"X","Y")

Obviously amend the range as required.

Regards

[dcdeez]

So you want to return an "X" if any of the entries in that column contains "text"? You can adapt it slightly to this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER):

=IF(SUM(--ISNUMBER(SEARCH("text",A1:A1000)))>0,"X","Y")

Obviously amend the range as required.

Regards

I want it to search for "text" specifically. Not any text.
The column it is searching is the description column and if that column has any rows that have the word "text" in it then I want it to return a "Y".

[XOR LX]

Did you try the formula I posted? That will search for the string "text", not any text.

Also, your original post states "if a row contains a specific value then to return x, if not return y", which contradicts what you have just now said - you can obviously work out which part of the the formula to change, I presume?

[dcdeez]

Yes. Your formula returned "The formula contains an invalid character sequence '-'."

If I change it to =IF(SUM(ISNUMBER(SEARCH("text",A1:A1000)))>0,"X","Y") then it returns "Operator or expression ':' is not supported in this context"

If I change it to =IF(SUM(ISNUMBER(SEARCH("text",[A])))>0,"X","Y") then it returns "The SUM function only accepts a column reference as an argument."

[dcdeez]

Did you try the formula I posted? That will search for the string "text", not any text.

Also, your original post states "if a row contains a specific value then to return x, if not return y", which contradicts what you have just now said - you can obviously work out which part of the the formula to change, I presume?

Yes. Your formula returned "The formula contains an invalid character sequence '-'."

If I change it to =IF(SUM(ISNUMBER(SEARCH("text",A1:A1000)))>0,"X","Y") then it returns "Operator or expression ':' is not supported in this context"

If I change it to =IF(SUM(ISNUMBER(SEARCH("text",[A])))>0,"X","Y") then it returns "The SUM function only accepts a column reference as an argument."

[XOR LX]

What are you using? PowerPivot? This sounds like DAX Syntax error or something?

[dcdeez]

What are you using? PowerPivot?

Probably would have been better if I mentioned that originally... But yes this is in a PowerPivot Window. Is this not possible then?

[dcdeez]

I actually just solved it.
= IF(SEARCH("text",[A],,0),"X","Y")


Thanks for your help.

[dilipandey]

Great

Please mark this thread as [SOLVED].. thanks.


Regards,
DILIPandey
<click on below * if this helps>