{"id":67893,"date":"2006-02-23T13:41:00","date_gmt":"2006-02-23T13:41:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/02\/23\/how-can-i-format-an-excel-spreadsheet-so-it-retains-leading-zeroes\/"},"modified":"2006-02-23T13:41:00","modified_gmt":"2006-02-23T13:41:00","slug":"how-can-i-format-an-excel-spreadsheet-so-it-retains-leading-zeroes","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-format-an-excel-spreadsheet-so-it-retains-leading-zeroes\/","title":{"rendered":"How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I format an Excel spreadsheet so that it retains leading zeroes?

— AS<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, AS. You know, one of the Scripting Guys still has nightmares about the number zero. Several years ago he got a phone call from a friend of his wondering if he\u2019d be interested in helping coach a youth league basketball team (10- and 11-year-olds). Never being one to look before leaping, this Scripting Guy said, \u201cSure.\u201d<\/P>\n

As it turned out, this team was put together at the last minute, and consisted of all the players none of the other coaches in the league wanted. (They were all 10-year-olds, too, in a league dominated by 11-year-olds.) After losing the opening game 14-12 – and sparking a na\u00efve bit of optimism – the team was shut out in each of the next three games.<\/P>\n

Yes, you heard correctly: shut out. Shut out in basketball<\/I>, the same game in which Wilt Chamberlain once scored 100 points all by himself. No points, nothing, nada, zip. In other words, all zeroes.<\/P>\n

Incidentally, if you\u2019re ever looking for a way to knock yourself down a peg or two, we\u2019d suggest being the coach of a basketball team that goes three consecutive games without scoring a single point. That humbles you in a hurry.<\/P>\n

Fortunately, AS, leading zeroes in Excel are less likely to damage your self-esteem. We\u2019re assuming you want to add a few numbers – say, the numbers 1 through 10 – to a spreadsheet, and then have those numbers formatted something like this:<\/P>

001\n002\n003\n004\n005\n006\n007\n008\n009\n010\n<\/PRE>\n
Can you do that? Well, like we said, when it comes to putting up zeroes, at least one of the Scripting Guys is a world-class expert:<\/P>
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Add()\nSet objWorksheet = objWorkbook.Worksheets(1)<\/p>\n
For i = 1 to 10\n    objExcel.Cells(i, 1).Value = i\nNext<\/p>\n
Set objRange = objWorksheet.UsedRange\nobjRange.NumberFormat = “000”\n<\/PRE>\n
As seems to always be the case with Excel, this is actually a very simple little script. The first four lines of code, for example, are boilerplate: all they do is create a visible instance of Excel, and then give us a blank workbook and a blank worksheet to work with. We then have this block of code that simply runs through cells A1 through J1 and places a number (1 through 10, corresponding to the row number) in each cell:<\/P>
For i = 1 to 10\n    objExcel.Cells(i, 1).Value = i\nNext\n<\/PRE>\n
In other words, our spreadsheet will have numbers that look like this:<\/P>
1\n2\n3\n4\n5\n6\n7\n8\n9\n10\n<\/PRE>\n
That\u2019s a good point: we don\u2019t want<\/I> our numbers to look like that, do we? That\u2019s why the script includes these last two lines of code:<\/P>
Set objRange = objWorksheet.UsedRange\nobjRange.NumberFormat = “000”\n<\/PRE>\n
This is where all the fun takes place. In the first line, we create a Range<\/B> object that encompasses all the cells on the spreadsheet that contain data (that\u2019s what the UsedRange<\/B> property gives us). After we\u2019ve created this range we then set the value of the NumberFormat<\/B> property to 000. That will give us our leading zeroes. In other words:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n
The number 1 will be displayed as 001.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The number 37 will be displayed as 037.<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
The number 442 will be displayed as, well, 442<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
That\u2019s all there is to it. If we wanted to display numbers using more zeroes then all we have to do add a few extra zeroes to the NumberFormat. For example, this line of code displays numbers using a maximum of six zeroes:<\/P>
objRange.NumberFormat = “000000”\n<\/PRE>\n
It\u2019s that easy.<\/P>\n
Incidentally, we have a confession to make. At the beginning of this column we said that the youth basketball team lost its first game 14-12. That\u2019s not entirely true. The Scripting Guy\u2019s friend received a phone call on a Friday asking if he\u2019d be willing to coach the team; the caller said, \u201cOh, and your first practice is tomorrow.\u201d<\/P>\n
\u201cGreat,\u201d said the friend. \u201cWhen\u2019s our first game?\u201d<\/P>\n
\u201cLast night. But because you didn\u2019t show up you had to forfeit.\u201d<\/P>\n
In other words, the team was 0-1 before anyone even knew it was<\/I> a team. That tells you everything you need to know about that season.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I format an Excel spreadsheet so that it retains leading zeroes?— AS Hey, AS. You know, one of the Scripting Guys still has nightmares about the number zero. Several years ago he got a phone call from a friend of his wondering if he\u2019d be interested in helping coach a […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[48,49,3,5],"class_list":["post-67893","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I format an Excel spreadsheet so that it retains leading zeroes?— AS Hey, AS. You know, one of the Scripting Guys still has nightmares about the number zero. Several years ago he got a phone call from a friend of his wondering if he\u2019d be interested in helping coach a […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=67893"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67893\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=67893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}