Using the inch symbol as a condition in a if statement

[David GCTW]

Hi,

I'm trying to write a simple if statement to determine if a cell value is 12" or 14" but I'm getting an error because of the inch symbol. For example =if(A1 = "12"", 1,If(A1 = "14"",2,"-")). You can see the double quotes at the end of each search criteria in the formula, how do I tell excel to ignore the first "?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(A1 = "12""", 1,IF(A1 = "14""",2,"-"))

 

[Rick Rothstein]

Fluff has given you the correct syntax for the way you were attempting to construct the formula. An alternate way of writing the formula is this...

=IF(A1 = 12&"""", 1,IF(A1 =14&"""",2,"-"))

The problem you ran into is that a (single) quote mark has a special meaning to Excel (and VBA as well)... it defines the start and end of a text constant... everything between them is to be taken as the characters they are with no special meaning (so if the letters LEN were inside a pair of quote marks, it would not be interpreted as the length function, just as three letters L, E and N). However, if you want to represent a quote mark character inside a text constant, you cannot use just a single quote mark for it because then Excel (and VBA as well) would not be able to tell if you are trying to end a text constant or if you meant that single quote mark to be just a single quote mark character. So, the syntax requires you to use two quote marks next to each other which Excel (and VBA as well) will interpret as a single quote mark character. So, in Fluff's formula, what I highlighted in red below (I also made them slightly bigger) represent a single quote mark and the black ones represent the beginning and end of text constants.

=IF(A1 = "12""", 1,IF(A1 = "14""",2,"-"))

Using the same color scheme, here is how my formula would look...

=IF(A1 = 12&"""", 1,IF(A1 =14&"""",2,"-"))[

 

[jasonb75]

Here's another alternative.

=IF(A1=12&CHAR(34),1,IF(A1=14&CHAR(34),2,"-"))

If there are going to be more that 2 options in the final formula then there are likely going to be better methods to use, a lookup table in the sheet is most often the best one to use.
Although not really a practical method, an alternative formula following the same pattern as your example which serves the same purpose as 5 nested IF's.

=IFERROR(MATCH(A1,{12,14,16,18,20}&CHAR(34),0),"-")