Embedded Dec2hex help needed

M

mogulboy

need help:
I am copying a bunch of 12 digit unique IDs over from Notepad into excel and
I then need to convert a portion (the first 11 digits) of the number into
HEX.
Some of the unique IDs have one or two zeros in the beg of the values:
008453518958
011144250839
So I am using the DEC2HEX(Left(A2,11)) but I am not getting the right
results. It seems to be ignoring the zeros so for the first unique ID it is
converting 8453518958...basically I want to ignore the last digit but am
having issues with the zeros
 
H

Harlan Grove

mogulboy said:
I then need to convert a portion (the first 11 digits) of the number into
HEX. Some of the unique IDs have one or two zeros in the beg of the values:
008453518958
011144250839
So I am using the DEC2HEX(Left(A2,11)) but I am not getting the right
results. It seems to be ignoring the zeros so for the first unique ID it
is converting 8453518958...basically I want to ignore the last digit but
am having issues with the zeros

If these values are numbers formatted as "000000000000" (12 0s in sequence),
then the cell only APPEARS with the leading 0s, but they're not part of the
cell's contents or its value. You can ensure these numbers are converted
correctly by including a TEXT call.

=DEC2HEX(LEFT(TEXT(A2,"000000000000"),11))
 
D

David Biddulph

If you want to ignore the last digit, use
=DEC2HEX(LEFT(A2,LEN(A2)-1))

Remember that when Excel stores numbers as numbers, it doesn't store leading
zeroes (even if the cell is formatted to display them).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top