Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

beginning zeros disappear in Excel 2007


  • Please log in to reply
7 replies to this topic

#1 godzilla1347

godzilla1347

  • Members
  • 344 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Long Island, NY
  • Local time:08:54 PM

Posted 30 November 2015 - 12:35 AM

I'm running Windows Vista on a Dell XPS 410 desktop with Excel 2007. I need to enter numbers starting with zero in the column field and they when I leave the cell, so do those zeros. I have set that column to text and the zeros remain but as soon as other activity goes on, it appears that Excel defaults to removing those beginning zeros again and I have to change the settings each time in the number group to text or use the format painter to copy a cell where the zeros remain intact.

 

Is there a way of setting the defaults so that the beginning zeros remain unless I change the settings? This is a big nuissance and I assume there's an easy fix but I can 't find it.

 

Thanks, 

 

Mark



BC AdBot (Login to Remove)

 


#2 Zahid Iqbal1

Zahid Iqbal1

  • Members
  • 22 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:06:54 AM

Posted 30 November 2015 - 05:12 AM

Yes I have the same problem in excel 2007 and never found any solution of this.



#3 Platypus

Platypus

  • Moderator
  • 13,667 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Australia
  • Local time:11:54 AM

Posted 30 November 2015 - 06:35 AM

Does this help?:

https://support.office.com/en-us/article/How-to-handle-leading-zeros-and-precision-in-number-codes-57f15d52-5c75-4a67-ad35-394ae944d347

Top 5 things that never get done:

1.


#4 godzilla1347

godzilla1347
  • Topic Starter

  • Members
  • 344 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Long Island, NY
  • Local time:08:54 PM

Posted 30 November 2015 - 11:42 PM

Thanks, yes it does. I had thought I had sent out a response but it must have been on a reply email.

 

The only concern I have is that using the custom template allows you to include the initial zeros per custom form but not universally. For example, I am listing books on Amazon and books have an identifying number called and ISBN. These are both 10 and 13 digit numbers. I can set the initial zeros if I set the form to 10 characters. However, if I had a 13 digit number that I needed to address, that system doesn't work. Fortunately, in my case, there aren't 13 digit ISBN's that start with zero but this could be a problem for someone who needs a global way to set the initial zeros.



#5 Platypus

Platypus

  • Moderator
  • 13,667 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Australia
  • Local time:11:54 AM

Posted 01 December 2015 - 07:16 AM

The only time I had to consistently counter this was with a spreadsheet online warranty claim submission system, and some of the part numbers began with zero. Fortunately it was only a few claims a month, so I just used the apostrophe.


Top 5 things that never get done:

1.


#6 orlbuckeye

orlbuckeye

  • Members
  • 250 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Orlando, Florida
  • Local time:09:54 PM

Posted 01 December 2015 - 03:06 PM

Format the column as text before you enter the number.  Excel by default thinks the cell is a number whe you enter numbers and leading 0's are insignificant and dropped.


Edited by orlbuckeye, 01 December 2015 - 03:08 PM.

  • Alienware 18 Intel Core i7 4810 QM 16GB DDR3
  • 18.4" (1920 x1080) 
  • 1 TB Samsung EVO MSATA SSD 1 TB WD 5400 RPM HD 750 GB WD 5400 RPM HD
  • Nvidia GT 860 GM SLI graphics
  • Windows 10 Pro + Surface Book i5 256gb SSD 8gb RAM DGPU

 


#7 godzilla1347

godzilla1347
  • Topic Starter

  • Members
  • 344 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Long Island, NY
  • Local time:08:54 PM

Posted 02 December 2015 - 03:47 AM

The problem is that the when I format the column to text, it works until a new entry goes into the column and then reverts back.  I'm using the apostrophe now and while it's still annoying, it's better than what i was doing.

 

I also have noticed that 13 digit ISBN numbers that don't start with zeros revert to some coded number once I leave the cell. Very annoying. I have to use the format painter to format that cell from one that is correct.  I'm wondering if later versions of Excel have made this easier as this is ridiculous.



#8 Zahid Iqbal1

Zahid Iqbal1

  • Members
  • 22 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:06:54 AM

Posted 06 December 2015 - 02:01 AM

Yes it solve my problem. Thanks a lot






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users