The OCT2HEX function in Excel converts an octal number (base 8, which uses only the digits 0 to 7) into a hexadecimal number (base 16, which uses 0 to 9 and A to F). For example, it turns the octal number 377 into FF.
You’ll mostly run into octal in computer-related data, like character code tables and values copied from code. OCT2HEX turns those numbers into hex in one step, so you don’t have to go through decimal first.
There are two things to watch out for, though. OCT2HEX can’t take a range of cells directly (there’s a simple fix for that), and it reads any 10-digit octal number that starts with 4 to 7 as a negative number.
Follow along with the example file
OCT2HEX Function Examples.xlsx
OCT2HEX Function Syntax
Here is the syntax of the OCT2HEX function:
=OCT2HEX(number, [places])
- number – the octal number you want to convert. It can have up to 10 digits (only 0 to 7), and can be a number, text in double quotes, or a cell reference. A 10-digit number that starts with 4 to 7 is read as a negative number (Example 4 shows how this works).
- places – (optional) the number of characters you want in the result. OCT2HEX adds leading zeros to reach this length.
When you leave out places, OCT2HEX uses as few characters as it needs. So =OCT2HEX(144) returns 64, while =OCT2HEX(144,4) returns 0064.
If places is smaller than the result needs, you get a #NUM! error. For example, =OCT2HEX(377,1) returns #NUM! because 377 converts to FF, which needs two characters.
A places value of 0 or less also returns #NUM!, and text like “two” returns #VALUE!. A decimal value is cut down to a whole number, so 2.9 works like 2.
The OCT2HEX function works in Excel for Microsoft 365, Excel 2024, 2021, 2019, 2016, and Excel for Mac.
When to Use the OCT2HEX Function
Use this function when you need to:
- Convert octal numbers to hexadecimal without going through decimal first.
- Get hex codes of a fixed length, with leading zeros.
- Turn octal values copied from code (like 0o17) into hex values (like 0xF).
- Convert negative octal numbers stored in 10-digit form.
Let me show you a few practical examples of how to use the OCT2HEX function.
Example 1: Convert a List of Octal Numbers to Hex
Let’s start with a simple example.
Below is a list of octal numbers, and I want to convert each one to hexadecimal in column B.
Here is the formula:
=OCT2HEX(+A2:A11)
You enter this formula once in cell B2, and the results spill down to B11. OCT2HEX converts every octal number in A2:A11 to hex, so 1750 becomes 3E8 and 23420 becomes 2710.
Important: Don’t skip the plus sign (+) before A2:A11. OCT2HEX can’t take a range reference directly, so =OCT2HEX(A2:A11) returns a single #VALUE! error instead of ten results.
The plus sign makes Excel read the values out of the range first and pass them to OCT2HEX as a list (an array), which it can work with.
OCT2HEX isn’t the only function that behaves like this, and here’s a look at other Excel functions that won’t spill on a range.
That article also uses the double minus (–) and +0 tricks. These turn each value into a number, which works here because octal numbers are made of digits only (they’d fail on text like the 0o17 values in Example 3).
Also notice that the results sit on the left of the cells. That’s because OCT2HEX returns text, not a number, even when a result looks like one (like 64 or 2710).
If you are using Excel 2019 or earlier, enter the below formula in cell B2 and then copy it down the range:
=OCT2HEX(A2)
Since each copy of this formula points to just one cell, it doesn’t need the plus sign. You can use the same copy-down approach for the other examples in this article.
Example 2: Get Two-Digit Hex Codes With Leading Zeros
Now let’s use the places argument.
Every character you type has a code number, and some ASCII charts (like the one on the Linux ascii manual page) list these codes in octal. On the web, the same codes are usually written as two-digit hex, like the %20 you see in place of a space in some web addresses.
Below is a list of characters with their octal codes, and I want the hex code for each one in column C, always two digits long.
Here is the formula:
=OCT2HEX(+B2:B11,2)
The 2 tells OCT2HEX to make every result two characters long, so it adds a leading zero where needed. For example, the octal code for Tab is 11, which converts to 9 and comes back as 09.
Codes that already have two hex digits stay as they are. Space has the octal code 40, which converts to 20 (the same 20 you see in %20).
Since OCT2HEX returns text, these leading zeros stay in the cell. You don’t need any number formatting to keep them.
Two digits are enough for any octal code up to 377, which converts to FF. A bigger code, like 400, needs three hex digits, so with places set to 2 it would return #NUM!.
Example 3: Convert Octal Values That Start With 0o
In Python code, octal numbers are written with 0o in front (like 0o17), and hex numbers with 0x in front (like 0xF). OCT2HEX doesn’t understand the 0o prefix, so =OCT2HEX("0o17") returns #NUM!.
Below is a list of octal values, most of them with the 0o prefix, and I want them as 0x-style hex values in column B.
Here is the formula:
="0x"&OCT2HEX(SUBSTITUTE(A2:A11,"0o",""))
How this formula works:
- The SUBSTITUTE function replaces “0o” with nothing, so 0o377 becomes 377.
- OCT2HEX then converts the cleaned octal numbers to hex, so 377 becomes FF.
- “0x”& adds 0x in front of each result, so you get 0xFF.
The value in A4, 017, doesn’t have the 0o prefix, so SUBSTITUTE leaves it alone. That’s how languages like C write octal (with just a leading 0), and OCT2HEX handles it fine because a leading zero doesn’t change the number.
You don’t need the plus sign here, because the SUBSTITUTE function already hands OCT2HEX a list of values rather than a range reference.
The SUBSTITUTE function is case-sensitive, so a value written with a capital O (like 0O17) keeps its prefix and returns #NUM!. If your data has both, use ="0x"&OCT2HEX(SUBSTITUTE(LOWER(A2:A11),"0o","")) instead, which turns everything to lower case before removing the prefix.
Example 4: Convert Negative Octal Numbers With OCT2HEX
Now let’s look at negative numbers, which work a bit differently.
OCT2HEX reads a 10-digit octal number that starts with 4 to 7 as a negative number. This is called two’s complement notation, and in it, 7777777777 is -1, 7777777776 is -2, and so on, counting down.
Below is a list of octal numbers along with the decimal value each one stands for, and I want their hex versions in column C, three characters long.
Here is the formula:
=OCT2HEX(+A2:A9,3)
The positive numbers work the same way as in Example 2. 17 returns 00F, and 144 returns 064.
The negative ones come back as 10-character hex numbers, even though I asked for three characters. For example, 7777777777 (which is -1) returns FFFFFFFFFF.
A 10-character hex number that starts with 8 to F is negative too, so trimming its leading characters would change its value. That’s why OCT2HEX ignores places for negative numbers and always returns 10 characters.
For example, 7777777634 (which column B shows as -100) returns FFFFFFFF9C. If you convert that back with the HEX2DEC function, =HEX2DEC("FFFFFFFF9C") returns -100 again.
The last row, 4000000000 (-536,870,912), returns FFE0000000. That’s the smallest negative number OCT2HEX can handle, because it’s the lowest 10-digit octal number that starts with 4.
Important: OCT2HEX always reads a 10-digit octal number that starts with 4 to 7 as negative, even if you meant it as a positive number, and it gives no error. For those, use the BASE and DECIMAL method in Example 5.
Example 5: Convert Octal Numbers Past OCT2HEX’s Limit
Let’s step it up with a case OCT2HEX can’t handle on its own.
OCT2HEX accepts up to 10 digits, but the largest positive number it converts is 3777777777 (536,870,911 in decimal), which becomes 1FFFFFFF. As you saw in Example 4, the 10-digit numbers from 4000000000 up are read as negative.
So bigger octal numbers run into two problems. A 10-digit number from 4000000000 to 7777777777 gives a negative hex result without any error, and a number with 11 or more digits returns #NUM!.
Below is a list of large octal numbers, and I want to try OCT2HEX in column B and then get the right hex values with a method that works in column C.
Here is the OCT2HEX formula:
=OCT2HEX(+A2:A8)
Only the first row gives the result you’d expect. The next three come back as 10-character negative values (for example, 7346545000 returns FFFB9ACA00), and the last three return #NUM!.
Now here is the formula that works:
=BASE(DECIMAL(A2:A8,8),16)
How this formula works:
- DECIMAL(A2:A8,8) converts each octal number to a regular decimal number. The 8 tells it the input is in base 8 (octal), so 7346545000 becomes 1000000000.
- The BASE function then converts that decimal number to base 16, which is hex. So 1000000000 becomes 3B9ACA00.
The DECIMAL function accepts a range directly, so this formula doesn’t need the plus sign. If you want leading zeros, the BASE function has its own third argument for the length, for example =BASE(DECIMAL(A2:A8,8),16,12).
This method works for octal numbers up to 377777777777777777 (18 digits). Enter numbers longer than 15 digits as text, though, because Excel keeps only 15 digits of a number and rounds the rest.
The BASE and DECIMAL functions treat every number as positive. So 7777777777 returns 3FFFFFFF with this method, while OCT2HEX reads it as -1 and returns FFFFFFFFFF.
Use this method when your octal numbers are meant to be positive, and OCT2HEX when your data uses 10-digit negative numbers. The BASE and DECIMAL functions are available in Excel 2013 and later versions.
Example 6: Why OCT2HEX Returns the #NUM! Error
Lastly, let’s look at what OCT2HEX does with inputs it can’t use.
Below are some inputs that commonly cause trouble, with a note on what’s in each cell, and I want to see what OCT2HEX returns for each one in column C.
Here is the formula:
=OCT2HEX(+A2:A9)
128 and 1290 return the #NUM! error because an octal number can only use the digits 0 to 7. 12345670123 returns #NUM! because it has 11 digits, one more than OCT2HEX allows.
The value in A5 returns #NUM! because it has a space before 755, which is easy to miss when you paste data from somewhere else. OCT2HEX doesn’t ignore the space.
The TRIM function removes it, so =OCT2HEX(TRIM(A5)) returns 1ED.
-17 and 17.5 return #NUM! too, because OCT2HEX doesn’t accept a minus sign or a decimal point. Negative numbers have to be in the 10-digit form shown in Example 4.
The last row shows that text input works fine. 0017 is stored as text with two leading zeros, and OCT2HEX reads it as 17 and returns F.
Important: An empty cell doesn’t return an error. OCT2HEX treats it as 0 and returns 0, which looks like a valid result, so make sure every input cell has a value.
So these are a few examples of how to use the OCT2HEX function in Excel. Just remember the plus sign when you convert a whole range, and keep an eye on 10-digit numbers that start with 4 to 7.
If you need to go the other way, the HEX2OCT function converts hex back to octal. And if you need decimal or binary instead, Excel has the OCT2DEC and OCT2BIN functions.
I hope you found this tutorial useful.
Other Excel Articles You May Also Like:
