Sorting using HEX

[Guest]

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.

 

[Frank Kabel]

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

 

[Ron Rosenfeld]

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

 

[Harlan Grove]

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

 

[Ron Rosenfeld]

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