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.
RIGHT returns the last character or characters in a text string, based on the number of characters you specify in the variable
RIGHT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
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 converts a hexadecimal number to decimal.
SUBSTITUTE(text, old_text, new_text, [instance_num]) – Substitutes
old_text in a text string. You can use
SUBSTITUTE 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. Use
REPLACE 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.