Leading zeros

How does one force Excel 2003 to retain leading zeros? This is related to my
earlier messages on "Sorting problem".

One option is to use a number to binary conversion. Each binary number has 8
bits, either 1's of 0's, and all bits are significant so leading 0's must be
retained. The conversion will simply be a lookup table of number and associated
binary equivalent and use the number as a cell reference to get the binary
equivalent.

Another option is to insert leading 0's in numbers that have only 1 or 2 digits
to convert all numbers to 3 digit numbers.

The only way I have thought of is to define the cell as being text rather than a
number.

--

Cheers . . . JC

JC wrote:
> How does one force Excel 2003 to retain leading zeros? This is related to my
> earlier messages on "Sorting problem".
>
> One option is to use a number to binary conversion. Each binary number has 8
> bits, either 1's of 0's, and all bits are significant so leading 0's must be
> retained. The conversion will simply be a lookup table of number and associated
> binary equivalent and use the number as a cell reference to get the binary
> equivalent.
>
> Another option is to insert leading 0's in numbers that have only 1 or 2 digits
> to convert all numbers to 3 digit numbers.
>
> The only way I have thought of is to define the cell as being text rather than a
> number.
>
---------------------

Internally Excel carries numbers as .... well, as numbers. No number
has leading zeros as you are asking. The formatted *presentation* of a
number can have leading zeros, but that's just Excel humoring you by
pasting them on the front for a poor dumb human to look at. In it's
heart Excel knows that numbers don't have leading zeros -- as a
mathematician also knows the same thing. Therefore it's not about to
use leading zeros in a sort (though I can't quite see why you'd want it
to anyhow as it wouldn't affect the outcome).

If you want to force leading zeros to be carried internally then you're
right, you need to store text not a number.

Bill

On Sat, 29 Jan 2005 18:46:52 -0500, "Bill Martin -- (Remove NOSPAM from
address)" <[email protected]> wrote:

> JC wrote:
> > How does one force Excel 2003 to retain leading zeros? This is related to my
> > earlier messages on "Sorting problem".
> >
> > One option is to use a number to binary conversion. Each binary number has 8
> > bits, either 1's of 0's, and all bits are significant so leading 0's must be
> > retained. The conversion will simply be a lookup table of number and associated
> > binary equivalent and use the number as a cell reference to get the binary
> > equivalent.
> >
> > Another option is to insert leading 0's in numbers that have only 1 or 2 digits
> > to convert all numbers to 3 digit numbers.
> >
> > The only way I have thought of is to define the cell as being text rather than a
> > number.
> >
> ---------------------
>
> Internally Excel carries numbers as .... well, as numbers. No number
> has leading zeros as you are asking. The formatted *presentation* of a
> number can have leading zeros, but that's just Excel humoring you by
> pasting them on the front for a poor dumb human to look at. In it's
> heart Excel knows that numbers don't have leading zeros -- as a
> mathematician also knows the same thing. Therefore it's not about to
> use leading zeros in a sort (though I can't quite see why you'd want it
> to anyhow as it wouldn't affect the outcome).
>
> If you want to force leading zeros to be carried internally then you're
> right, you need to store text not a number.
>
Bill

The problem I have is sorting on IP addresses for which each of the a.b.c.d
numbers is in the range 0 - 255. I get the following occuring:-

Source:211.177.154.134, 1030, WAN -
Source:211.19.215.70, 1051, WAN -
Source:211.19.215.70, 1677, WAN -
Source:211.190.195.138, 2876, WAN -

when I would prefer to get this:-

Source:211.19.215.70, 1051, WAN -
Source:211.19.215.70, 1677, WAN -
Source:211.177.154.134, 1030, WAN -
Source:211.190.195.138, 2876, WAN -

Since Excel appears to sort on character position left to right adding in
leading 0's would make 019 a lower number than 177 and should result in the sort
running correctly.

Another option that I may have to resort to is to convert the numbers to the 8
bit binary equivalent using a lookup table in another worksheet and then sort on
that. Again the problem is that leading 0's are important and need to be
retained - i.e. 19 =00010011.

Perhaps this all may be simplified if I split up the a.b.c.d into 4 helper
columns and sort based on them. Would this sort correctly? I would need a
macro to sort on 4 criteria but that may be simple to write.

--

Cheers . . . JC

> The problem I have is sorting on IP addresses for which each of the a.b.c.d
> numbers is in the range 0 - 255. I get the following occuring:-
>
> Source:211.177.154.134, 1030, WAN -
> Source:211.19.215.70, 1051, WAN -
> Source:211.19.215.70, 1677, WAN -
> Source:211.190.195.138, 2876, WAN -
>
> when I would prefer to get this:-
>
> Source:211.19.215.70, 1051, WAN -
> Source:211.19.215.70, 1677, WAN -
> Source:211.177.154.134, 1030, WAN -
> Source:211.190.195.138, 2876, WAN -
>
> Since Excel appears to sort on character position left to right adding in
> leading 0's would make 019 a lower number than 177 and should result in the sort
> running correctly.
>
> Another option that I may have to resort to is to convert the numbers to the 8
> bit binary equivalent using a lookup table in another worksheet and then sort on
> that. Again the problem is that leading 0's are important and need to be
> retained - i.e. 19 =00010011.
>
> Perhaps this all may be simplified if I split up the a.b.c.d into 4 helper
> columns and sort based on them. Would this sort correctly? I would need a
> macro to sort on 4 criteria but that may be simple to write.
>

----------------

The number 19 is already lower than 177 even without a leading zero. If
you've broken the IP address into four cells then it sorts correctly.
Try it. Manually take the little 4 IP address example you gave and
divide it into four cells for each address. Then sort them as I said,
starting from the fourth cell as the key, then do it again with the
third, then do it again with the second, then do it again with the
first. It works. Try it. You don't need any macros or binary or anything.

If you want to do it with custom macros then you can add the leading
zeros and do it with one sort rather than four. It's a lot of
programming effort though to end up at the same place as the built in
functions already get you to.

Bill

On Sun, 30 Jan 2005 21:53:27 -0500, "Bill Martin -- (Remove NOSPAM from
address)" <[email protected]> wrote:

> > The problem I have is sorting on IP addresses for which each of the a.b.c.d
> > numbers is in the range 0 - 255. I get the following occuring:-
> >
> > Source:211.177.154.134, 1030, WAN -
> > Source:211.19.215.70, 1051, WAN -
> > Source:211.19.215.70, 1677, WAN -
> > Source:211.190.195.138, 2876, WAN -
> >
> > when I would prefer to get this:-
> >
> > Source:211.19.215.70, 1051, WAN -
> > Source:211.19.215.70, 1677, WAN -
> > Source:211.177.154.134, 1030, WAN -
> > Source:211.190.195.138, 2876, WAN -
> >
> > Since Excel appears to sort on character position left to right adding in
> > leading 0's would make 019 a lower number than 177 and should result in the sort
> > running correctly.
> >
> > Another option that I may have to resort to is to convert the numbers to the 8
> > bit binary equivalent using a lookup table in another worksheet and then sort on
> > that. Again the problem is that leading 0's are important and need to be
> > retained - i.e. 19 =00010011.
> >
> > Perhaps this all may be simplified if I split up the a.b.c.d into 4 helper
> > columns and sort based on them. Would this sort correctly? I would need a
> > macro to sort on 4 criteria but that may be simple to write.
> >
>
> ----------------
>
> The number 19 is already lower than 177 even without a leading zero. If
> you've broken the IP address into four cells then it sorts correctly.
> Try it. Manually take the little 4 IP address example you gave and
> divide it into four cells for each address. Then sort them as I said,
> starting from the fourth cell as the key, then do it again with the
> third, then do it again with the second, then do it again with the
> first. It works. Try it. You don't need any macros or binary or anything.
>
> If you want to do it with custom macros then you can add the leading
> zeros and do it with one sort rather than four. It's a lot of
> programming effort though to end up at the same place as the built in
> functions already get you to.
>
Bill,

I set up a test file comprising the original data and then added 9 helper
columns to insert the 4 IP address components in columns H - K. I then sorted
on col H, then col I and then col J which resulted in a file which almost is
perfect. You're right - leading 0's are not needed.

I really need to sort on cols H, I, J, K and A (date and time) to get things in
the right order which will take a macro as Excel only allows for a 3 layer sort.

Interestingly, The file has 2843 rows of data at the moment but I put in code to
calculate the 9 helper columns to row 3000 (which should cater for most months)
and the file size jumped from 570Kb to 2.93Mb. All of the helper columns
follow the pattern =if($a1="","",MID($C1,$L1+1,$M1-$L1-1)).

Any help on how to set up the macro for a 5 column sort would be appreciated?
You've been a huge help already!

One wrinkle that the macro will need to cater for is that the data is being
derived from a text string so the numbers in the cols H - K and A are all text .
When I set up the sort on cols H, I and J Excel asked if I wanted the text
numbers treated as numbers - the macro will need to cater for this as well.

I assume that a macro can be written in say Notepad and then somehow associated
with the excel spreadsheet. The only macro I have written so far was one in
which I saved the keystrokes and then edited from within Excel with the
associated spreadsheet open.

I can see that I'll have to get myself a good book on Excel macros and do some
reading on the topic.
--

Cheers . . . JC

> Bill,
>
> I set up a test file comprising the original data and then added 9 helper
> columns to insert the 4 IP address components in columns H - K. I then sorted
> on col H, then col I and then col J which resulted in a file which almost is
> perfect. You're right - leading 0's are not needed.
>
> I really need to sort on cols H, I, J, K and A (date and time) to get things in
> the right order which will take a macro as Excel only allows for a 3 layer sort.
>
> Interestingly, The file has 2843 rows of data at the moment but I put in code to
> calculate the 9 helper columns to row 3000 (which should cater for most months)
> and the file size jumped from 570Kb to 2.93Mb. All of the helper columns
> follow the pattern =if($a1="","",MID($C1,$L1+1,$M1-$L1-1)).
>
> Any help on how to set up the macro for a 5 column sort would be appreciated?
> You've been a huge help already!
>
> One wrinkle that the macro will need to cater for is that the data is being
> derived from a text string so the numbers in the cols H - K and A are all text .
> When I set up the sort on cols H, I and J Excel asked if I wanted the text
> numbers treated as numbers - the macro will need to cater for this as well.
>
> I assume that a macro can be written in say Notepad and then somehow associated
> with the excel spreadsheet. The only macro I have written so far was one in
> which I saved the keystrokes and then edited from within Excel with the
> associated spreadsheet open.
>
> I can see that I'll have to get myself a good book on Excel macros and do some
> reading on the topic.

-----------------

If you want to use a macro to do the 4 (or 5) sorts, the easiest thing
is to just record it from the keyboard/mouse.

1) Click on Tools>Macro>RecordNewMacro which will open up a window
asking what you want to name the macro. Enter the name and a letter
(e.g. "s" for sort) then click "ok". At that point a tiny window opens
which you can temporarily ignore.

2) Just go through the manual steps to sort the data all 4 or 5 times to
get the final result you want.

3) Go back to that tiny window and click "stop" or if it's not visible,
go back to Tools>Macro>StopRecording.

At this point you can execute the macro and sort everything just by
entering Ctrl-s. Note that upper and lower case "s" will be treated as
different macros.

If you want to see the macro and/or go in to edit it and change things
about, then click Tools>Macro>Macros and select the one you want then
click "edit". As far as I know the easiest way to work with macros is
with this VBA editor. You can apparently import/export files into the
editor but I've always just used the built in capability since it is a
development system that also flags incorrect statements, lets you step
through code, etc. You'll see the code looks pretty much like simple
BASIC, but with a little different syntax in spots. You can open up
macros from the example *.XLS files that came with Excel to see other
examples of how it works. There's also a reasonably good help system
built into that VBA editor that supplies some assistance with the
commands, etc.

I've found I can bumble my way through this VBA macro language just with
some prior BASIC experience and the help system built into the editor.
If you want to get into more heavy duty VBA programming, then I'm happy
enough with Walenbach's book "Excel 2003 Power Programming with VBA".

Good luck...

Bill