Excel matching IF statement not evaluating correctly

Question

I have two columns in the following structure

     A    B
1    49   4922039670

I have been able to evaluate =LEN(A1) as 2, =LEFT(B1,2) as 49, and =LEFT(B1,LEN(A1)) as 49. However when I try to evaluate =IF(LEFT(B1,LEN(A1))=A1,TRUE,FALSE) it evaluates FALSE. I have no idea why. I have tried to change both columns A and B to different matching formats but it still just evaluates FALSE.

Not entirely sure what Im doing wrong.

The easiest solution for provided case is =IF(LEFT(B1,LEN(A1))*1=A1,TRUE,FALSE). — zipa
– zipa, Commented Oct 19, 2016 at 15:46

am2 · Accepted Answer · 2016-10-19 15:45:46Z

3

maybe it is depending on the Version but in my Excel it you cast implicit your number 4922... to a string and 49 <> '49'

Either you make both strings or both numbers, f.e.:

=IF(LEFT(B1,LEN(A1))=""&A1,TRUE,FALSE)

""&<a number> concates an empty string to a number, the result will be a string you could use function "TEXT" or - if you are sure, both are numbers - use 'VALUE'

=IF(VALUE(LEFT(B1,LEN(A1)))=A1,TRUE,FALSE)

to make a text should be more stable

edited Oct 19, 2016 at 15:45

answered Oct 19, 2016 at 15:36

am2

3718 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex K. · Accepted Answer · 2016-10-19 15:37:29Z

1

Comparing a string (what LEFT gives you) with an integer is going to be FALSE

=LEFT(B1,LEN(A1))=49  //FALSE

You want

=LEFT(B1,LEN(A1))="49"  //TRUE

So:

=LEFT(B1,LEN(A1))=TEXT(A1, "0")

Or:

=EXACT(LEFT(B1,LEN(A1)),A1)

answered Oct 19, 2016 at 15:37

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Collectives™ on Stack Overflow

Excel matching IF statement not evaluating correctly

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related