Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
On Sat, 29 Jan 2005 18:46:52 -0500, "Bill Martin -- (Remove NOSPAM from
address)" 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
On Sun, 30 Jan 2005 21:53:27 -0500, "Bill Martin -- (Remove NOSPAM from
address)" 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 |
#6
|
|||
|
|||
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 ToolsMacroRecordNewMacro 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 ToolsMacroStopRecording. 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 ToolsMacroMacros 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 |
#7
|
|||
|
|||
On Mon, 31 Jan 2005 10:29:49 -0500, "Bill Martin -- (Remove NOSPAM from
address)" wrote: 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 ToolsMacroRecordNewMacro 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 ToolsMacroStopRecording. 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 ToolsMacroMacros 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 I recorded a 3 column sort macro and then followed the pattern to add keys 4 and 5 to the sort. This causes an Run-time error 1004 - Application-defined or Object-defined error. Other changes I made was to change Range("A538").Activate to Range("A1).Activate and Range("A538").Select to Range("A1").Select as these positions will only apply to this test file while A1 will apply whatever the size of the file. The debug editor is highlighting the part starting with Selection.Sort..... and ending with DataOption5:..... I can't see anything in the code that looks wrong to me. This begs the question - will Excel allow a 5 layer sort when its built in system only allows nesting 3 layers? Sub Sort() ' ' Sort Macro ' Macro recorded 1/02/2005 by John Clarke ' ' Keyboard Shortcut: Ctrl+s ' Range("A1:P3678").Select Range("A1").Activate Selection.Sort Key1:=Range("H1"), Order1:=xlAscending, Key2:=Range("I1") _ , Order2:=xlAscending, Key3:=Range("J1"), Order3:=xlAscending, _ Key4:=Range("K1"), Order4:=xlAscending, Key5:=Range("A1"), _ Order5:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _ DataOption3:=xlSortTextAsNumbers, DataOption4:=xlSortTextAsNumbers, _ DataOption5:=xlSortTextAsNumbers Range("A1").Select End Sub -- Cheers . . . JC |
#8
|
|||
|
|||
JC wrote:
I recorded a 3 column sort macro and then followed the pattern to add keys 4 and 5 to the sort. This causes an Run-time error 1004 - Application-defined or Object-defined error. Other changes I made was to change Range("A538").Activate to Range("A1).Activate and Range("A538").Select to Range("A1").Select as these positions will only apply to this test file while A1 will apply whatever the size of the file. The debug editor is highlighting the part starting with Selection.Sort..... and ending with DataOption5:..... I can't see anything in the code that looks wrong to me. This begs the question - will Excel allow a 5 layer sort when its built in system only allows nesting 3 layers? Sub Sort() ' ' Sort Macro ' Macro recorded 1/02/2005 by John Clarke ' ' Keyboard Shortcut: Ctrl+s ' Range("A1:P3678").Select Range("A1").Activate Selection.Sort Key1:=Range("H1"), Order1:=xlAscending, Key2:=Range("I1") _ , Order2:=xlAscending, Key3:=Range("J1"), Order3:=xlAscending, _ Key4:=Range("K1"), Order4:=xlAscending, Key5:=Range("A1"), _ Order5:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _ DataOption3:=xlSortTextAsNumbers, DataOption4:=xlSortTextAsNumbers, _ DataOption5:=xlSortTextAsNumbers Range("A1").Select End Sub ------------------ I don't know if what you've done is legal or not -- I've never tried it. I suspect it's not legal since the manual version only allows three levels. Open up the VBA editor, put the cursor on the sort command and hit F1. That brings up a help screen which implies only three levels can be done. What I would do is simply what I said before. Do the sort twice -- once with three levels and once with two. Just record it that way and then go in to look at the code and see how they handled it. It works. Try it. Bill |
#9
|
|||
|
|||
On Mon, 31 Jan 2005 20:57:49 -0500, "Bill Martin -- (Remove NOSPAM from
address)" wrote: JC wrote: I recorded a 3 column sort macro and then followed the pattern to add keys 4 and 5 to the sort. This causes an Run-time error 1004 - Application-defined or Object-defined error. Other changes I made was to change Range("A538").Activate to Range("A1).Activate and Range("A538").Select to Range("A1").Select as these positions will only apply to this test file while A1 will apply whatever the size of the file. The debug editor is highlighting the part starting with Selection.Sort..... and ending with DataOption5:..... I can't see anything in the code that looks wrong to me. This begs the question - will Excel allow a 5 layer sort when its built in system only allows nesting 3 layers? Sub Sort() ' ' Sort Macro ' Macro recorded 1/02/2005 by John Clarke ' ' Keyboard Shortcut: Ctrl+s ' Range("A1:P3678").Select Range("A1").Activate Selection.Sort Key1:=Range("H1"), Order1:=xlAscending, Key2:=Range("I1") _ , Order2:=xlAscending, Key3:=Range("J1"), Order3:=xlAscending, _ Key4:=Range("K1"), Order4:=xlAscending, Key5:=Range("A1"), _ Order5:=xlAscending, Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _ DataOption3:=xlSortTextAsNumbers, DataOption4:=xlSortTextAsNumbers, _ DataOption5:=xlSortTextAsNumbers Range("A1").Select End Sub ------------------ I don't know if what you've done is legal or not -- I've never tried it. I suspect it's not legal since the manual version only allows three levels. Open up the VBA editor, put the cursor on the sort command and hit F1. That brings up a help screen which implies only three levels can be done. What I would do is simply what I said before. Do the sort twice -- once with three levels and once with two. Just record it that way and then go in to look at the code and see how they handled it. It works. Try it. Bill I think you are correct in that Excel will only allow a 3 layer sort. If you run a macro with 3 sort keys and then click on the Sort option the 3 keys used in the macro are selected. The rows must remain intact. What I am trying to do is sort the order in which the complete rows appear so that like entries appear together. Two independent sorts won't necessarily achieve that. I have got around the 3 level sort restriction by changing the sort helpers from 4 helpers H=a, I=b, J=c and K=d to 2 helpers H=1000*a+b and I=1000*c+d where a.b.c.d is the IP address. This automatically inserts leading 0's in the b and d components as required and allows me to sort on the IP address and date. Thank you for your help with this problem. You've given me the clues to sort through the coding and end up with the result I wanted. Take a bow, Sir! -- Cheers . . . JC |
#10
|
|||
|
|||
JC wrote:
I think you are correct in that Excel will only allow a 3 layer sort. If you run a macro with 3 sort keys and then click on the Sort option the 3 keys used in the macro are selected. The rows must remain intact. What I am trying to do is sort the order in which the complete rows appear so that like entries appear together. Two independent sorts won't necessarily achieve that. I have got around the 3 level sort restriction by changing the sort helpers from 4 helpers H=a, I=b, J=c and K=d to 2 helpers H=1000*a+b and I=1000*c+d where a.b.c.d is the IP address. This automatically inserts leading 0's in the b and d components as required and allows me to sort on the IP address and date. Thank you for your help with this problem. You've given me the clues to sort through the coding and end up with the result I wanted. Take a bow, Sir! ------------- Two separate sorts will do it very well. Just try it -- you'll see. That's the technique that's been taught in computer science for the last 30 years at least. The rows remain intact if you tell Excel to sort the entire rows based on the keys in the one column of interest. Even when Excel is told to do the three level sort, I suspect under the covers it's just doing the simple one column sort independently three times. That would be the simplest way to code it and possibly the fastest. Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format a cell to keep leading zeros. | New Users to Excel | |||
Displaying leading zeros in an Excel spreadsheet | Excel Discussion (Misc queries) | |||
Leading Zeros in Numeric Values | Excel Worksheet Functions | |||
How do I display leading zeros so I can export a fixed in Excel? | Excel Discussion (Misc queries) | |||
Insert Leading Zeros | Excel Worksheet Functions |