Software

Ask something
Question Locked

excel format problem

0 helpful
2 replies

by smuscanto

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am using a text format for an ID number because of a leading zero. I am also using vlookup to transfer info from a source worksheet to a destination worksheet within the same workbook. However, when I enter an ID number with a leading zero, vlookup can't find a match even though there is one. If I copy and paste the ID number from the source worksheet then it finds a match. I used an IF function to determine if the ID number in the source worksheet is the same as the one I entered in the destination worksheet and they are NOT. However, they are both formatted as text and they look identical. Is there a way I can determine why Excel thinks they are different?

BTW, ID numbers without a leading zero work perefectly.

2 replies

by tig2

And why do that when you can use the format dialog to allow a leading "0" by selecting "Custom" and mandating a string length? That would keep the field formatted as number and insure that the string length is consistent.

I have had Excel refuse to do things that I know it should be able to do. Generally there is some "invisible" formatting in the way. On those occasions, I will delete whatever is causing the problem and regenerate the field new.

See if a mandated string length doesn't fix this. And Good Luck!

This discussion is closed to new replies.