Solved: Excel 2007 Hide Leading Zero

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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?
 
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?
 

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.
 

Attachments

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.
 
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.
 
Joined
Jul 25, 2004
Messages
5,458
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. :)
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top