Sorting using HEX

B

BobD

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.
 
F

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
 
R

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.
Click to expand...

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
 
H

Harlan Grove

Ron Rosenfeld said:
If your HEX numbers are greater than 7FFFFFFFFF post back and I will
post a VBA routine that can convert larger HEX numbers.
Click to expand...

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

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)))
Click to expand...

Given your stated limits, there would be no need to use VBA.



--ron
 
Top