Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting using HEX
How do you sort a column of data that is in hexidecimal? I sort by column and
it scrambles because it is in hex. Thak you. |
#2
|
|||
|
|||
Hi
one idea (not tested though) use in a helper column the formula =HEX2DEC(A1) copy this for all rows and sort with this column Note: You need to install the Analysis Toolpak Addin for this -- Regards Frank Kabel Frankfurt, Germany "BobD" schrieb im Newsbeitrag ... How do you sort a column of data that is in hexidecimal? I sort by column and it scrambles because it is in hex. Thak you. |
#3
|
|||
|
|||
On Wed, 10 Nov 2004 09:18:01 -0800, "BobD"
wrote: How do you sort a column of data that is in hexidecimal? I sort by column and it scrambles because it is in hex. Thak you. Convert the data to decimal in a helper column, then sort on that column. You can use the HEX2DEC worksheet function. If you get the #NAME error, look at HELP for that function. If your HEX numbers are greater than 7FFFFFFFFF post back and I will post a VBA routine that can convert larger HEX numbers. --ron |
#4
|
|||
|
|||
"Ron Rosenfeld" wrote...
.... If your HEX numbers are greater than 7FFFFFFFFF post back and I will post a VBA routine that can convert larger HEX numbers. Why bother with VBA? 15 decimal digits of precision means the largest hexadecimal number that could be accomodated without having to resort to string-based arbitrary precision is 38D7EA4C68000 (=10^15 decimal). Assuming smaller hexadecimal numbers should always be treated as unsigned, built-in functions suffice. =IF(LEN(x)<9,HEX2DEC("0"&x), HEX2DEC("0"&LEFT(x,LEN(x)-9))*16^9+HEX2DEC("0"&RIGHT(x,9))) |
#5
|
|||
|
|||
On Thu, 11 Nov 2004 01:01:19 -0800, "Harlan Grove" wrote:
"Ron Rosenfeld" wrote... ... If your HEX numbers are greater than 7FFFFFFFFF post back and I will post a VBA routine that can convert larger HEX numbers. Why bother with VBA? 15 decimal digits of precision means the largest hexadecimal number that could be accomodated without having to resort to string-based arbitrary precision is 38D7EA4C68000 (=10^15 decimal). Assuming smaller hexadecimal numbers should always be treated as unsigned, built-in functions suffice. =IF(LEN(x)<9,HEX2DEC("0"&x), HEX2DEC("0"&LEFT(x,LEN(x)-9))*16^9+HEX2DEC("0"&RIGHT(x,9))) Given your stated limits, there would be no need to use VBA. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) | |||
Sorting problem | Excel Discussion (Misc queries) | |||
sorting question | Excel Discussion (Misc queries) | |||
Macro for sorting different rows | Excel Worksheet Functions |