1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 Hide Leading Zero

Discussion in 'Business Applications' started by computerman29642, Oct 9, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have an Excel spreadsheet that contains project numbers. In order for me to get the numbers to sort correctly, there must be a leading zero.

    Is there a way to hide the leading zero on the project numbers?


    Here are some of the numbers...

    100-233
    32-231
    55-987
    92-1999
     
  2. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    So basically you need leading 0's in order to sort, but you don't want them to actually be displayed in the cell?
     
  3. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    jonvan, welcome to the forum. :)

    Yes, that would be correct.
     
  4. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    Hmm, odd request. I'm not sure of a way to hide certain digits from the beginning of a cell.

    Excel should be able to sort in numerical order with or without a padded number though.

    What happens when you sort them without leading 0's?
     
  5. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Take a look at the sample file. My original actually has more columns, but this should give you an idea of what happens.

    Now you will notice that there is an ID at the very bottom (second to last) that is out of order. Try to sort the IDs, and you will see that they do not sort correctly.
     

    Attached Files:

  6. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    Hmm, I see what you mean, it is putting the 92-### through 99-### at the bottom.

    I assume these numbers are for the year? If so, couldn't you just use the actual four digit year. If it displayed 1992 rather than 92 it would sort correctly. If that's the case, you could do a simple search and replace through that column to add the other two digits of the year.
     
  7. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Unfortunately, that will not work.
     
  8. jonvan

    jonvan

    Joined:
    Oct 9, 2008
    Messages:
    66
    Hmm, not sure then. I can't think of a simple solution. Maybe someone with some VB knowledge could help you out.
     
  9. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    jonvan, I appreciate the suggestion and attempt. :)
     
  10. AcidBurnz95

    AcidBurnz95

    Joined:
    Jul 17, 2008
    Messages:
    85
    Use the custom number format ####-000 and just type in the number without the dashes
     
  11. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    AcidBurnz95, that worked perfectly. Thank you. :)
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    If there will always be at least one place holder to the left of the dash you can use ###0-000 as your format. Pound signs are a conditional place holder, while zeros are constant place holders. :)
     
  13. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Zack, there should always be a place holder left of the dash. I will gibe your suggestions a try.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/757613

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice