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

**rbrian** · 10-05-2013, 04:29 AM

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.

**BadlySpelledBuoy** · 10-05-2013, 04:33 AM

Have a look at the link below. Does this help you at all?
http://chandoo.org/wp/2012/09/05/sho...nts-if-needed/

**martindwilson** · 10-05-2013, 06:37 AM

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

**FDibbins** · 10-05-2013, 04:08 PM

Maybe try formatting your data as text?

**rbrian** · 12-02-2013, 08:23 AM

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.