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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
It works for me.
________________________________________________________________________
 
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.
 
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
 
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
 
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,"")
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count only if 7
Amcrest ProHD IP2M-841B Wireless IP Camera 7
Help adding text values 7
Compare data set 1 with data set 2. Add to data set 2 if not found 0
Please Help If statement Data Location!!!!! 1
IF statement to check for numerical value 3
Array If statement doesn't work 5
Nikon is back with the swivel in S4! 1

Back
Top