-1

I am working on a fairly simple worksheet where if a cell contains a hexadecimal value I want to convert it to decimal, otherwise if it is already in decimal format we just leave it. Something like:

=IF(A1 is hex, HEX2DEC(A1),A1)

Any ideas/suggestions would be awesome. Thanks in advance.

I have searched around a bit but can not seem to find anything.

user20555729
  • 1
  • 2
  • If you do `Hex2Dec` on non hexadecimal, what is the output? IIRC it's an error, so maybe just do `Iferror(hex2dec(A1),A1)`? – BruceWayne Nov 20 '22 at 16:33
  • I just tried that but it converts it to another different decimal number, but thanks. – user20555729 Nov 20 '22 at 16:47
  • 2
    There is no way for Excel to know what is decimal and what is Hex. 123 entered as hex is a different decimal value then 123 entered as decimal. Excel will not know the difference the intent of the number when it is entered. – Scott Craner Nov 20 '22 at 17:14
  • Thanks Scott, good to know. The other issues I am trying to figure out like I added in a comment below is a formula that can do the hex to decimal conversion when the number is longer than 10 characters. – user20555729 Nov 20 '22 at 17:21
  • See @tomSharpe answer here: https://stackoverflow.com/questions/67165952/how-to-convert-big-numbers-between-hex-and-dec-in-excel-without-using-vba – Scott Craner Nov 20 '22 at 18:18

2 Answers2

0

This formula will convert the number from hexadecimal to decimal, if it is possible to convert from hexadecimal to decimal:

=IFERROR(HEX2DEC(A1), A1)

To my knowledge, there isn't a way to distinguish a hexadecimal number from a decimal number, if the hexadecimal number only has digits [0-9] unless of course you have a narrower definition of what your input is.

Jakob Nielsen-MSFT
  • 504
  • 2
  • 8
  • If the number is in hex it changes it correctly however if the number is a decimal this formula changes it to a different number. It essentially used the DEC2HEX formula which if used on a number that is already a decimal will change it to something altogether different. But thanks – user20555729 Nov 20 '22 at 16:53
  • 1
    Curious what you define a hexadecimal number as. Obviously, A1B2 is a hex number, but so is 1234. So more information about your input is needed to give you a better answer. – Jakob Nielsen-MSFT Nov 20 '22 at 17:03
  • This is a sample of the number I am trying to convert: 301806c841d0 which should convert to 52879751135696. So I am struggling to solve two issues with this, forst is trying to ID the string as hex and the other is converting this longer string to decimal in Excel. The HEX2DEC will only do 10 characters. – user20555729 Nov 20 '22 at 17:13
  • Do you know if the numbers should be in a certain range/have certain number of digits? – Tom Sharpe Nov 20 '22 at 17:29
  • The numbers the hex represents can vary from 1-14 characters. – user20555729 Nov 20 '22 at 17:35
  • You can deal with longer hex numbers than 10 characters by splitting them, but as with other contributors I can't see how you can identify numbers which are actually hex but only consist of digits 0-9. – Tom Sharpe Nov 20 '22 at 17:43
0

Looks like being able to identify a particular string as text is not possible but I came up with a workaround. And the answer on how to convert longer strings worked!

Thanks everyone for the prompt input.

user20555729
  • 1
  • 2