Alphanumeric string conversion into hexadecimal then report the number of bytes

Give us some examples of the data that you start with. Are these decimal numbers, or hexadecimal? I'm not sure how you would start with alphanumeric characters if they are decimal numbers, and if they are hexadecimal then why would you need to convert them?

Your example formula seems to be taking the ASCII code of each character in turn - this is basically a 7-bit code, but often uses 8 bits (i.e. 1 byte) to give up to 256 codes. Thus the number of bytes is the number of characters that you start with (i.e. =LEN(K132) ).

I'm not sure what you want to do.

Pete

Originally Posted by Pete_UK

Give us some examples of the data that you start with. Are these decimal numbers, or hexadecimal? I'm not sure how you would start with alphanumeric characters if they are decimal numbers, and if they are hexadecimal then why would you need to convert them?

Your example formula seems to be taking the ASCII code of each character in turn - this is basically a 7-bit code, but often uses 8 bits (i.e. 1 byte) to give up to 256 codes. Thus the number of bytes is the number of characters that you start with (i.e. =LEN(K132) ).

I'm not sure what you want to do.

Pete

Pete, terribly sorry for the delay (life got crazy for a few days).

Here's what I am attempting to do:


My input cell (K25) in alphanumberic:
ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890+-*/.?!()@,"'

My output cell (L25) converting K25 into hexadeximal (result, not calculation):
4142434445464748494A4B4C4D4E4F505152535455565758595A313233343536373839302B2D2A2F2E3F212829402C222720

My byte count cell (D25) (calculation then result)
=IF(K25="","0",LEN(L25)/2)
Result of 50

My issue was with my output hexadecimal conversion (formula):
=DEC2HEX(CODE(MID(K25,1,1)))&DEC2HEX(CODE(MID(K25,2,1)))&DEC2HEX(CODE(MID(K25,3,1)))&DEC2HEX(CODE(MID(K25,4,1)))&DEC2HEX(CODE(MID(K25,5,1)))&DEC2HEX(CODE(MID(K25,6,1)))&DEC2HEX(CODE(MID(K25,7,1)))&DEC2HEX(CODE(MID(K25,8,1)))&DEC2HEX(CODE(MID(K25,9,1)))&DEC2HEX(CODE(MID(K25,10,1)))&DEC2HEX(CODE(MID(K25,11,1)))&DEC2HEX(CODE(MID(K25,12,1)))&DEC2HEX(CODE(MID(K25,13,1)))&DEC2HEX(CODE(MID(K25,14,1)))&DEC2HEX(CODE(MID(K25,15,1)))&DEC2HEX(CODE(MID(K25,16,1)))&DEC2HEX(CODE(MID(K25,17,1)))&DEC2HEX(CODE(MID(K25,18,1)))&DEC2HEX(CODE(MID(K25,19,1)))&DEC2HEX(CODE(MID(K25,20,1)))&DEC2HEX(CODE(MID(K25,21,1)))&DEC2HEX(CODE(MID(K25,22,1)))&DEC2HEX(CODE(MID(K25,23,1)))&DEC2HEX(CODE(MID(K25,24,1)))&DEC2HEX(CODE(MID(K25,25,1)))&DEC2HEX(CODE(MID(K25,26,1)))&DEC2HEX(CODE(MID(K25,27,1)))&DEC2HEX(CODE(MID(K25,28,1)))&DEC2HEX(CODE(MID(K25,29,1)))&DEC2HEX(CODE(MID(K25,30,1)))&DEC2HEX(CODE(MID(K25,31,1)))&DEC2HEX(CODE(MID(K25,32,1)))&DEC2HEX(CODE(MID(K25,33,1)))&DEC2HEX(CODE(MID(K25,34,1)))&DEC2HEX(CODE(MID(K25,35,1)))&DEC2HEX(CODE(MID(K25,36,1)))&DEC2HEX(CODE(MID(K25,37,1)))&DEC2HEX(CODE(MID(K25,38,1)))&DEC2HEX(CODE(MID(K25,39,1)))&DEC2HEX(CODE(MID(K25,40,1)))&DEC2HEX(CODE(MID(K25,41,1)))&DEC2HEX(CODE(MID(K25,42,1)))&DEC2HEX(CODE(MID(K25,43,1)))&DEC2HEX(CODE(MID(K25,44,1)))&DEC2HEX(CODE(MID(K25,45,1)))&DEC2HEX(CODE(MID(K25,46,1)))&DEC2HEX(CODE(MID(K25,47,1)))&DEC2HEX(CODE(MID(K25,48,1)))&DEC2HEX(CODE(MID(K25,49,1)))&DEC2HEX(CODE(MID(K25,50,1)))

This is a rather long roundabout way of getting my alphanumeric calculation to convert into hexadecimal, and it fails if I have more or less than 50 alphanumeric characters.

I am hoping to have a hexadecimal conversion of my alphanumeric string (ranging from 1 to 65515 characters) without having to have the 'messy' code for each individual character up to a set amount. This coding is rather cumbersome to execute (as I would have to know upfront how many characters/bytes I will have, though it could be done with the LEN function on my alphanumeric string).

Respectfully,
Talamon

Well, you are going to struggle - from XL Help (Specifications and Limits):

Total number of characters that a cell can contain :- 32,767 characters

Length of formula contents :- 8,192 characters

Internal length of formula :- 16,384 bytes

etc.

So, you can never have 65515 characters in your string and you wouldn't be able to evaluate such a long string in one formula as each term in your conversion formula (i.e. between your &'s) will need about 30 characters.

Perhaps you could do 4 characters at a time using a formula like this in L25:

=DEC2HEX(CODE(MID($K$25,(ROWS($1:1)-1)*4+1,1)))&DEC2HEX(CODE(MID($K$25,(ROWS($1:1)-1)*4+2,1)))&DEC2HEX(CODE(MID($K$25,(ROWS($1:1)-1)*4+3,1)))&DEC2HEX(CODE(MID($K$25,(ROWS($1:1)-1)*4+4,1)))

and then copy this down. You might need some IFs in there to test for the length of the string, dependent on (ROWS($1:1)-1)*4+x. Then in M25 you can have this:

=L25

and in M26 you can have this formula:

=M25&L26

which can then be copied down. The final result will be in the bottom cell of column M.

Hope this helps.

Pete