confusion using LEFT in an IF statement

[matoco1]

ok, I have an If statement which needs to look at a cell, identify the leading number and depending on the condition perform 1 INDEX MATCH or another.

Basically the data spans 2 sheets.

The reference it is looking at is in the format 0001/17, 0002/17

So I have set the If to use LEFT(B2) which correctly returns a 0 in this case. the problem I have is that the If statement is seeing that as > 4 and unless my maths are really off that's not right.

I can write the formula with the condition backwards but my need to understand the issue is more important, any ideas?

[Tony Valko]

The LEFT function returns a TEXT value even if it looks like a number.

"0" = a text value

In Excel TEXT values are greater than numeric values.

="0">4 = TRUE

"0" and 0 are not the same!

So, you need to convert the TEXT value "0" to the numeric value 0.

One way to do that...

=--LEFT(B2)

[Melvinrobb]

The cell interprets the field as a text value, so the character your left function is pulling is a text value as well.

instead of left(b2) try (left(b2)+0)

this converts the text value to a number value.