February 23rd, 2006

How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?

Hey, Scripting Guy! Question

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

— AS

SpacerHey, Scripting Guy! AnswerScript Center

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’d 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, “Sure.”

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ïve bit of optimism – the team was shut out in each of the next three games.

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

Incidentally, if you’re ever looking for a way to knock yourself down a peg or two, we’d suggest being the coach of a basketball team that goes three consecutive games without scoring a single point. That humbles you in a hurry.

Fortunately, AS, leading zeroes in Excel are less likely to damage your self-esteem. We’re 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:

001
002
003
004
005
006
007
008
009
010

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:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

For i = 1 to 10 objExcel.Cells(i, 1).Value = i Next

Set objRange = objWorksheet.UsedRange objRange.NumberFormat = “000”

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:

For i = 1 to 10
    objExcel.Cells(i, 1).Value = i
Next

In other words, our spreadsheet will have numbers that look like this:

1
2
3
4
5
6
7
8
9
10

That’s a good point: we don’t want our numbers to look like that, do we? That’s why the script includes these last two lines of code:

Set objRange = objWorksheet.UsedRange
objRange.NumberFormat = “000”

This is where all the fun takes place. In the first line, we create a Range object that encompasses all the cells on the spreadsheet that contain data (that’s what the UsedRange property gives us). After we’ve created this range we then set the value of the NumberFormat property to 000. That will give us our leading zeroes. In other words:

The number 1 will be displayed as 001.

The number 37 will be displayed as 037.

The number 442 will be displayed as, well, 442

That’s 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:

objRange.NumberFormat = “000000”

It’s that easy.

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’s not entirely true. The Scripting Guy’s friend received a phone call on a Friday asking if he’d be willing to coach the team; the caller said, “Oh, and your first practice is tomorrow.”

“Great,” said the friend. “When’s our first game?”

“Last night. But because you didn’t show up you had to forfeit.”

In other words, the team was 0-1 before anyone even knew it was a team. That tells you everything you need to know about that season.

Author

0 comments

Discussion are closed.