CODEWITHSUNDEEP
excelfunctionsearchif-statement
crustycollins
crustycollins

Reputation: 92

Get a yes, no, or nothing out of a cell in Excel

Im trying to get either a Yes, No or nothing out of a cell in excel.

If i try the code below it will print "Yes" or " " but it will not print "No"

=IFERROR(IF(SEARCH("Y",F3),"Yes",IF(SEARCH("n",F3),"No","")),"")

cell F3 has 

Y - D3

or

N - D3

It works fine with the following code, but it won't search for the string

=IFERROR(IF(F3="Y","Yes",IF(F3="N","No","")),"")

"N" and "Y" work but "Y - D3" doesn't

Any comments are appreciated

Upvotes: 1

Views: 332

Answers (3)

Excel Hero
Excel Hero

Reputation: 14764

You do not need to use any IF() functions at all:

=REPT("Yes",LEFT(F3,1)="y")&REPT("No",LEFT(F3,1)="n")

This article might interest you: I Heart IF

Upvotes: 4

Mikey
Mikey

Reputation: 153

If you surround with an ISNUMBER then it should work.

Like so:

=IFERROR(IF(ISNUMBER(SEARCH("Y",F3)),"Yes",IF(ISNUMBER(SEARCH("N",F3)),"No","")),"")

Upvotes: 1

sous2817
sous2817

Reputation: 3960

If you step through the formula, you'll see that it's generating an error, which is short-circuiting your formula. Try wrapping the conditions in a Not and ISERROR and see if it gives you the expected results. Something like:

=IFERROR(IF(NOT(ISERROR(SEARCH("Y",F3))),"Yes",IF(NOT(ISERROR(SEARCH("n",F3))),"No","")),"")

I'm sure the formula could be simplified, but hopefully this at least gets your started...

Upvotes: 2

Related Questions