CODEWITHSUNDEEP
excelexcel-formula
JSArrakis
JSArrakis

Reputation: 799

Excel matching IF statement not evaluating correctly

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.

Upvotes: 1

Views: 130

Answers (2)

am2
am2

Reputation: 371

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

Upvotes: 3

Alex K.
Alex K.

Reputation: 175766

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)

Upvotes: 1

Related Questions