+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Angry 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.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,492

    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/

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,976

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

    Maybe try formatting your data as text?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    06-04-2012
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    7

    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.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,976

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

    Happy to help and thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Add Trailing Zeros
    By sweetkel23 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-07-2012, 01:42 AM
  2. Wrong value with trailing zeros
    By temba in forum Excel General
    Replies: 1
    Last Post: 10-01-2012, 05:03 AM
  3. [SOLVED] Delete trailing zeros.
    By krasher in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 09-21-2012, 10:43 AM
  4. Replies: 5
    Last Post: 04-09-2012, 02:28 PM
  5. trailing zeros
    By htorres in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-23-2007, 03:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1