Keep trailing zeros, but only if they're there...

I'm trying to use VLOOKUP to find framework positions. In theory it should be simple, but trailing zeros are driving me crazy! The raw data can be in two formats: either 12345 or 01.1234. These are actual real physical locations, not just numbers, so they have to be correct. The problem comes with, for example, 12.1200, which Excel insists on showing as 12.12, and refuses to find 12.1200 in the list, even when I can find it with Ctrl-F. If I format the cell as a number with four decimal places, it finds 12.1200 ok, but then it doesn't find 12345, because that's shown as 12345.0000. What can I do?

I have Excel 2010 in Windows 7, and if it makes a difference I can also use Exel 2011 on Mac, but I'd prefer to keep it on my work computer, since it has to be shared with Excel 2003 in Vista and 2010 in 7.

Re: Keep trailing zeros, but only if they're there...

Have a look at the link below. Does this help you at all?

http://chandoo.org/wp/2012/09/05/sho...nts-if-needed/

1 Attachment(s)

Re: Keep trailing zeros, but only if they're there...

this is not clear, so the data you are looking up is in the format

12345

01.1234

12.1200

and you wish to lookup

12.1200 in that list?

see attached for some different methods ,if none of these work post a sample workbook

Re: Keep trailing zeros, but only if they're there...

Maybe try formatting your data as text?

Re: Keep trailing zeros, but only if they're there...

Sorry it's been so long, but formatting as text worked - I extracted the data from a text string, using =mid(a1) for the decimals and =value(mid(a1)) for the whole numbers, and it all works ok. Thanks for your help.

Re: Keep trailing zeros, but only if they're there...

Happy to help and thanks for the feedback :)