Excel / Calc: Convert a Hexadecimal number to Decimal
The following examples allow you to convert hexadecimal
values of the format 0xYYYYYY
to decimal
using a spreadsheet editor like Calc
or Excel
.
The following codes will remove the first two characters (the value 0x
) of the cell B2
and then convert the result to decimal
using the HEX2DEC
function.
Using the RIGHT function
In this example, we used the RIGHT
function with the num_chars
parameter to be equal to the number of characters in the cell minus 2. This used to delete the 0x
value from the HEX
column by removing the first two characters of the cell.
To get the number of characters in the cell we use the LEN
function on the cell of interest.
=HEX2DEC(RIGHT(B2,LEN(B2)-2))
Using the SUBSTITUTE function
In the following example we used the SUBSTITUTE
function to automatically find the 0x
prefix of the HEX
value and delete it by replacing it with an empty string.
=HEX2DEC(SUBSTITUTE(B2,"0x",""))
Using the REPLACE function
The last example uses the REPLACE
function. Starting from the character in position 1 in the cell, it replaces the sub-string of size 2 with the empty string and thus deleting the prefix. Please note that this function is not zero-based
so the first character is at position 1 and not at position 0.
=HEX2DEC(REPLACE(B2,1,2,""))
Functions Legend:
RIGHT(text,[num_chars])
–RIGHT
returns the last character or characters in a text string, based on the number of characters you specify in the variablenum_chars
.RIGHT
always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.LEN(text)
–LEN
returns the number of characters in a text string. Again,LEN
always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.HEX2DEC(number)
–HEX2DEC
converts a hexadecimal number to decimal.SUBSTITUTE(text, old_text, new_text, [instance_num])
– Substitutesnew_text
forold_text
in a text string. You can useSUBSTITUTE
when you want to replace specific text in a text string.REPLACE(old_text, start_num, num_chars, new_text)
–REPLACE
replaces part of a text string, based on the number of characters you specify, with a different text string. UseREPLACE
when you want to replace any text that occurs in a specific location in a text string.REPLACE
always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.