Every day we deal with numbers with decimal notation, but there can be these rare occasions when we need to use other systems, such as hexadecimal, octal, and binary.
There are a lot of tutorials about number conversion on the Internet, so in this one, I will only how to convert how to do it in Excel with the built-in functions.
Convert DEC to HEX value
The Excel function you can use to convert a decimal number to hexadecimal is DEC2HEX(decimal).
As decimal value uses numbers from 0 to 9, hexadecimal uses the same values plus an additional six: A to F.
|
1 |
=DEC2HEX(255) |
This formula returns FF (the biggest hexadecimal number that uses only two characters).
The hex value is often used in computer graphics to represent the intensity of three basic colors: red, green, and blue (RGB), from #000000, which is black to #FFFFFF – white (max intensity of each color).
Convert HEX to DEC
Similarly, you can convert the value back from hexadecimal to decimal. This time you can use a similar-sounding function: HEX2DEC.
|
1 |
=HEX2DEC("FF") |
The result of this formula is 255.
Remember to take this value into parentheses if it contains a letter. With numbers, it’s not necessary.
HEX to OCT and vice versa
The octal number system is a positional notation numeral system that uses the digits 0 to 7. It is used in computing because it allows easy representation of binary numbers.
An example of a formula with an octal number:
|
1 |
=HEX2OCT("FF") |
This formula returns 377.
To convert it back, use this one:
|
1 |
=OCT2HEX(377) |
HEX to BIN and vice versa
Binary numbers are often used in computer science and mathematics. This notation is used for representing numerical values using two digits, 0 and 1.
|
1 |
=HEX2BIN("FF") |
This number equals 11111111 in binary notation.
If you try to convert binary numbers to hexadecimal use this function:
|
1 |
=BIN2HEX(11101) |
In hex notation it’s 1D.
Prefixes (0x) for hex values
Programming languages often use Hexadecimal values, which start with 0x. For example, 0xFF instead of just FF.
If you want to convert a hexadecimal number to decimal or any other notation, you will have to remove any 0x prefixes before converting it.
There are a few different methods you can use to do this: Flash Fill, Text to Columns, or even Find & Replace. However, the best long-term solution would be to create a formula that would solve the problem for you automatically.
That is what we’ll do:
|
1 |
=RIGHT(A2,LEN(A2)-2) |
This formula works in a way that it takes a value from cell A2 and keeps 2 characters (length of the text inside A2 – 2) from the right side.