The following examples allow you to convert
hexadecimal values of the format
decimal using a spreadsheet editor like
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
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.
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.
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.
RIGHTreturns the last character or characters in a text string, based on the number of characters you specify in the variable
RIGHTalways counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
LENreturns the number of characters in a text string. Again,
LENalways counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
HEX2DECconverts a hexadecimal number to decimal.
SUBSTITUTE(text, old_text, new_text, [instance_num])– Substitutes
old_textin a text string. You can use
SUBSTITUTEwhen you want to replace specific text in a text string.
REPLACE(old_text, start_num, num_chars, new_text)–
REPLACEreplaces part of a text string, based on the number of characters you specify, with a different text string. Use
REPLACEwhen you want to replace any text that occurs in a specific location in a text string.
REPLACEalways counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.