My IF function is not recognizing the results of my LEFT function.

**tblasko** · 03-23-2012, 05:38 PM

Wow, I'm so frustrated! Example spreadsheet is attached.

Why does the following formula return "False," instead of "True."

Formula in cell A1:
=IF(B1=C1,"True","False")

Value in B1:
2

Formula in C1:
=LEFT(D1,1)

Value in D1:
2

Result = "False" whaaaaat?

If I replace the 2 with a text character it works fine:

Formula in cell A1:
=IF(B1=C1,"True","False")

Value in B1:
"F"

Formula in C1:
=LEFT(D1,1)

Value in D1:
"F"

Result = "True" --- why does text work and not the number?!?! Typing the number in works fine and I tried matching up the formatting to see if that did it, no luck.

Here's my dilemma in more detail (if you can answer the above read no further): I'm trying to isolate all the values in a series of text strings that start with a certain digit, and end with certain digits, specifically I'm trying to test a string to see if the last 4 characters of a string are equal to 2012, and the first character is a 2. For example the bold entries would qualify:

1022012
1162012
1232012
1242011
1302012
2062012
2132012
2202012
2272012
3052012
3122012

I can't sort the list because values from 2011 are dispersed throughout.

So I have the following formula, which references two place holder cells that I can input the month and year I'm looking for referencing two cells that contain the month and date I'm currently looking for:

=IF(AND(LEFT(E4,1)=H2,RIGHT(E4,4)=I2),"True","False")

Where H2 has the "Month" value in it, in this case 2, and I2 has the year value, in this case 2012.

Thanks!
-Tim

**Paul** · 03-23-2012, 05:42 PM

Hi Tim, welcome to the forum.

When using IF and comparisons using =, the two values must be identical for a match to occur. The value (number) 2 is not the same as the text string "2". Anytime you use a LEFT, MID, RIGHT function the result is a String, not a number.

So you could use something like:

=IF(AND(LEFT(E4,1)+0=H$2,RIGHT(E4,4)+0=I$2),True,False)

The +0 converts a string containing a number to an actual number, which should match. Also, True and False don't need to be in quotes. An IF function will return True or False automatically unless you have some other value or function in those arguments.

**tblasko** · 03-26-2012, 10:05 AM

Paul, many thanks! I tried changing the formatting to a number manually for the result, thinking that it was a formatting issue, but it appears that you can't change the formatting of a formula result through the settings. I'm glad to know that you can use the "+0" to force it.

Thanks so much!!