How do I use an If= statement with an apostrophe (IF(s4="jack's",.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Denver Tiffany - 29 Jun 2007 16:16 GMT

I need to set a new cell's value to a numeric digit by saying :
If(s4="Jack's", 1, ""). But it won't work b/c of the apostrophe inside of
"Jack's".

Is there a way around this without altering the data?

Reply to this Message

Vasant Nanavati - 29 Jun 2007 16:21 GMT

It works for me.
________________________________________________________________________

>I need to set a new cell's value to a numeric digit by saying :
> If(s4="Jack's", 1, ""). But it won't work b/c of the apostrophe inside of
> "Jack's".
>
> Is there a way around this without altering the data?

Reply to this Message

peter - 29 Jun 2007 16:22 GMT

Make sure there are not extra spaces before or after Jack's. In Excel 2007,
I typed Jack's in one cell and added =IF(D4="Jack's",1,"") and I am getting a
1. If I add one empty space after 's I then get blank.

Signature

Peter

> I need to set a new cell's value to a numeric digit by saying :
> If(s4="Jack's", 1, ""). But it won't work b/c of the apostrophe inside of
> "Jack's".
>
> Is there a way around this without altering the data?

Reply to this Message

Bernie Deitrick - 29 Jun 2007 16:25 GMT

Tiffany,

=IF(S4="Jack's",1,"")

worked fine for me.

Try

=IF(TRIM(S4)="Jack's",1,"")

You may have extra spaces.... Otherwise, try retyping Jack's into cell S4

HTH,
Bernie
MS Excel MVP

Reply to this Message

Elkar - 29 Jun 2007 16:28 GMT

The apostrophe does not cause a problem in this formula. Since it is in
between the two double-quotes, it will be read as part of the text string.

Does your data in S4 include the quotes? If so, that is where the problem
lies. Try writing your formula like this instead:

=IF(S4="""Jack's""",1,"")

HTH,
Elkar

> I need to set a new cell's value to a numeric digit by saying :
> If(s4="Jack's", 1, ""). But it won't work b/c of the apostrophe inside of
> "Jack's".
>
> Is there a way around this without altering the data?

Reply to this Message

Teethless mama - 30 Jun 2007 14:28 GMT

The formulas below are much flexible. They will work with a word name Jack.
Example: "Jack ", " jack ", "Jack's", "Hello Jack", and so on....

=IF(ISNUMBER(SEARCH("Jack",A1)),1,"")
or
=IF(COUNTIF(A1,"*Jack*"),1,"")

> I need to set a new cell's value to a numeric digit by saying :
> If(s4="Jack's", 1, ""). But it won't work b/c of the apostrophe inside of
> "Jack's".
>
> Is there a way around this without altering the data?

Reply to this Message