Convert Hex Serial# to Decimal: HEX2DEX not matching system

[pete234]

I've really searched for this because it's really driving me nuts. I'm trying to validate a column for decimal string so I can make sure I submit valid serial numbers in my report. However, when I use HEX2DEX this conversion is not matching what is found in the billing system. I work at a telecom and it is not a very common process to do manually. Someone in an IT group once was able to do this outside of Excel but I was not privy to that process. The rules that I know is that each Hex is 8 characters and the decimal is 11 characters.

Example:
60305AC7
HEX2DEX=1613781703
Billing system shows

00407191266

Is this a straight conversion or is something else effecting this?

Thanks

 

[Norie]

When I use a calculator and enter the hex 60305AC7 and convert it to decimal I get the same result ie 1613781703

So I don't think this is a straight conversion.

What is the hex value meant to represent?

 

[pete234]

When I use a calculator and enter the hex 60305AC7 and convert it to decimal I get the same result ie 1613781703

So I don't think this is a straight conversion.

What is the hex value meant to represent?

The hex is an ESN of a cell phone. Oh, I found this realizing it may be telecom specific. Anyway to do this in Excel. First 2 are treated seperately?

To convert a cellular ESN from hexadecimal to decimal, use the following code. The base conversion is different due to the fact that the first two characters of a hexadecimal value must be converted seperately from the remaining six characters.
<?
function esnhexdec($hex){
$a = sprintf("%03d", hexdec(substr($hex, 0, 2)));
$b = sprintf("%08d", hexdec(substr($hex, 2, 6)));
return $a . $b;
}
?>

 

[Norie]

I still can't see how the conversion is working.

60 in hex is 96 in decimal and 305AC7 is 3168967.

 

[pete234]

What about this?

Is this a good key?

To convert between an 11-digit decimal ESN and an 8-digit hexadecimal ESN code, you must convert it in two parts. Convert the low-order (right-most) 6 hex digits into the low-order 8 decimal digits, and convert the high-order (left-most) 2 hex digits into the high-order 3 decimal digits.

So, for example, if your hex ESN is 60002000 you would convert it to decimal like this:

1) split it into two parts:
60, 002000

2) convert each part (and add leading zeros as necessary to get the right number of digits):
0x60 = 096
0x002000 = 00008192

3) recombine the two parts to get the decimal ESN:
09600008192

 

[fairwinds]

Hi,

Based on just your last post:

Try:

=TEXT(HEX2DEC(LEFT(A1,2)),"000")&TEXT(HEX2DEC(RIGHT(A1,6)),"00000000")

 

[pete234]

Oh, that worked excellent but now I realized my solution requested was not logical. I really needed a check on the hex first since I wanted to validate decimal to make sure it was 11. But forcing all to be 11 string makes that not valid except for short strings which are mainly for wrong alpha or short strings which I can filter on I think just on the Hex. That's my long way of saying thanks. However, I think I need to use the solution I found once about validating the characters in the hex and the length.