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.

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.

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

"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)))

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