Logic in mid function not working

[Hanky]

Hi There,

Greetings for the day!

I'm having transaction numbers in Col. A. All transaction numbers' 5th digit must be zero, however there may be zeros at other digits, 5th digits must be zero. To validate the same, i'm applying MID function in Col B as below:

Transaction number
TXNZ0XXX127 =MID(A2,5,1)=0

BY applying this logic, Why I'm getting "FALSE"?
Please help.
Thanks
Have a great day.

[AliGW]

Because it's text. Try this:

=VALUE(MID(A2,5,1))=0

Welcome to the forum, by the way!

[kersplash]

This is text you are testing for so put double quotes around zero.

Please Login or Register  to view this content.

[FDibbins]

Just to add in to what Ali said, LEFT, MID and RIGHT all always return a text answer, even if it looks like a number. A quick give-away is that by default, text is always left-aligned and real numbers are always right aligned.

If you extract a "number" from either a longer number or a text string, you need to convert that to a number
either or these should work...
=VALUE(MID(A2,5,1))=0
or
=--MID(A2,5,1)=0

[Hanky]

Hi,

Thanks AliGW for quick address and greatful to FDibbins for giving additional info. this helped a lot and will assist in future as well.
GOD BLESS
Have a great day

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

Hanky, thank you for the belated rep, always appreciated!!