Converting DEC to HEX in Excel data

[RU482]

I've got a line of data in an Excel file that is a (0-255) decimal number that needs to be converted to HEX numbers (00 - FF)to be understood. Anyone know an easy way to do that in excel?

 

[PowerEngineer]

choose(trunc(DEC/16),"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E") &
choose(mod(DEC,16),"0","1","2","3","4","5","6","7","8","9","A","B","C","D","E")

Both lines as one cell formula, where DEC refers to the cell with the decimal number in it. Must be between 0 and 255. Result is actually character string.

This should work (or at least be close) 🙂

 

[RU482]

doesn't seem to be working in Excel 97 ;(

EDIT: I take that back, it sort of works, just 1 digit off
example 1) DEC=17 result=00 , should be 11
example 2) DEC 119 result=66 , should be 77

EDIT 2: SOLUTION!!!! Add 17 to each DEC 🙂

Thanks much !!

 

[RossGr]

Adding 17 will not fix the given function. A couple of things need to be done to fix it. F, is the highest digit in hex so you need to add F to the end of the string, then once that is done, 1 is the smallest digit, so cut the 0 off the front. To get,

=CHOOSE(TRUNC(F6/16),"1","2","3","4","5","6","7","8","9","A","B","C","D","E","F")&CHOOSE(MOD(F6,16),"1","2","3","4","5","6","7","8","9","A","B","C","D","E","F")


This will give you correct results for most decimal numbers. It does not work if you have even multiples of 16, I get a #value error, seems the choose function wants results >0. I see no easy way to fix this. The posted function is not an optimal solution.

I would use a VB macro to do this, base conversion does not lend itself to the spreadsheet cell structure. If time allows I will post later with a reliable VB solution.

Edit:

Ok, I made it work in the cell

try

=IF(TRUNC(F6/16)=0,"0",(CHOOSE(TRUNC(F6/16),"1","2","3","4","5","6","7","8","9","A","B","C","D","E","F")))&IF(MOD(F6,16)=0,"0",CHOOSE(MOD(F6,16),"1","2","3","4","5","6","7","8","9","A","B","C","D","E","F"))