How to Convert Hexadecimal to Decimal in Excel [5 Top Methods]

This blog post will help you learn how to convert hexadecimal numbers to decimal numbers. Learn about the importance of understanding hexadecimal and explore different methods for converting hexadecimal numbers to decimal numbers.

When you work with Shapes Fill color or Text Font color in excel, you may notice the spin button input for Red, Green & Blue, and an input box for hexadecimal numbers in the Colors dialog box. The hexadecimal color follows the format #RRGGBB, where RR is red, GG is green, and BB is blue. The hexadecimal numbers can range from 00 to FF.

Let’s take a moment to understand the Number systems before you start learning how to convert Hexadecimal to Decimal in Excel.

Number systems are used in mathematics, engineering, and computer science. The most common number system is the decimal system, which is based on the number 10. Other systems include the binary system, which is based on the number 2; the octal system, which is based on the number 8; and the hexadecimal system, which is based on the number 16.

Numbers are written using the digits 0 through 9 in the decimal system. Each digit has a place value depending on its position in the number. For example, the number 852 has an 8 in the hundredth place, a 5 in the tens place, and a 2 in the one’s place.

Numbers are written using only the digits 0 and 1 in the binary system. Each digit has a place value depending on its position in the number. For example, the number 1001 has a 1 in the eighth place, a 0 in the fours place, a 0 in the two’s place, and a 1 in the one’s place.

The Hexadecimal numeral system, commonly abbreviated hex, is a set of 16 symbols. Each digit has a place value depending on its position. For example, the number #F2A has an F in the 256 place, a 2 in the 16 place, and an A in the one’s place.

Decimal is the common numeric system base 10, which uses the ten symbols 0, 1, 2, 3, 5, 6, 7, 8, and 9. The decimal integers and six additional symbols are used in hexadecimal. The letters A, B, C, D, E, and F from the English alphabet are used because there are no number symbols that represent values larger than nine. Decimal values of hexadecimal A and F are 10 and 15, respectively.

To avoid confusion with other numbering systems, hexadecimal numbers are sometimes suffixed with an h or prefixed with #, or 0x before the number.  For example, #23, 23h, and 0x23 mean 23 hexadecimal.

Number symbols:

In this article, you will learn how to use Basic functions, HEX2DEC function, Power Query, VBA, and Office Scripts to convert Hexadecimal to Decimal in Excel.

Get your copy of the example workbook used in this blog post to follow along!

Convert Hexadecimal to Decimal in Excel with Basic functions

You can use excel basic functions and operators to convert hexadecimal to decimal.

Steps to Convert a hexadecimal number to decimal eg.0xF2A.

1. Split the hexadecimal value into its digits F, 2, and A.
2. Convert each digit to its decimal equivalent F = 15, 2 = 2 and A = 10.
3. Multiply each digit by its place value 15*256 = 3840, 2*16 = 32, and 10 x 1 = 10.
4. Add the three values together 3840 + 32 + 10 = 3882.

Eg. Convert the Red Hexadecimal color code #FF0000 to RGB color code (255, 0, 0)

So, the RGB colors are: Red = FF₁₆ = 255₁₀ ; Green = 00₁₆ = 0₁₀ ; and Blue = 00₁₆ = 0₁₀

Convert Hexadecimal to Decimal in Excel with HEX2DEC function

Excel built-in function HEX2DEC to convert Hexadecimal number to a decimal number in excel.

HEX2DEC( number )

HEX2DEC converts a hexadecimal number to a decimal number.

Go to cell D2, type the formula =HEX2DEC( C2 ), and Press Enter. Drag the fill handle to the range D2:D6 to copy the formula.

If the hexadecimal numbers start with a # or h before the number. You remove the first character and then use the HEX2DEC function in Excel.

Excel RIGHT function helps you to remove the first character # or h, and then apply the HEX2DEC function.

=HEX2DEC( RIGHT( "#ff0000", LEN( "#ff0000") - 1 ))

If you receive the hexadecimal number start with 0x, 0xff0000. You have to remove the text 0x using the Substitute function or Replace function and use the HEX2DEC function.

=HEX2DEC( SUBSTITUTE( "0xff0000", "0x", "" ))

Or

=HEX2DEC( REPLACE( "0xff0000", 1, 2, "" ))

Convert Hexadecimal to Decimal in Excel with Power Query

Power Query is a powerful ETL tool that helps you easily connect to data sources, transform & clean the data, and create meaningful reports & visualizations. It accesses data from databases, text files, web pages, and more. Power Query can also be used to automate the data processing and formatting steps. Power Query user interface is easy to use, and it records each data processing step.

Steps to convert text to numbers using Power Query

1. Select the range of cells B1:C6 in the PowerQuery Sheet
2. Choose From Table/Range option in Data Tab – to open the Power Query window.

3. Select the Blank Query option in the Other Sources item in the Home Tab New Source command.

4. Select the Advanced Editor in Home Tab, to open the Advanced Editor dialog box to write the function.
5. Copy and paste the below script into the script area and Press Done.

(hexNum as text) as number =>
let
        mapValue = [0=0, 1=1, 2=2, 3=3, 4=4, 5=5, 6=6, 7=7, 8=8, 9=9, A=10, B=11, C=12, D=13, E=14, F=15],
        hexChar = Text.ToList(Text.Upper(hexNum)),
        noOfDigit = List.Count(hexChar)-1,
        base = 16,
        Result = List.Sum(
                        List.Transform(
                                {0..noOfDigit}
                                , each Record.Field(mapValue, hexChar{_}) * Number.Power(base, noOfDigit - _)
                                )
                        )
in
        Result

The first line declares the function, specifying that it takes a text as an input and returns a number as output. The script assigns an array to the variable mapValue for mappings Hexadecimal to Decimal (e.g. 0=0, A=10). The Text.ToList() function to convert the input hexadecimal number into a list of characters and the List.Count() function to count the number of digits in the input hexadecimal number. The base variable is set to 16, as Hexadecimal numbers are base 16.

Finally, The List.Sum() function is calculating the decimal equivalent of the input hexadecimal number. This is done by looping through each character and calculating the value of the digit in the number multiplied by the power of the base.

6. Select your table Table1
7. Click Invoke Custom function command in the Add Column Tab.

8. Enter your New column name Red Decimal, Select the Function query Query1, Select the hexadecimal column name Red Hex to convert, and then Press OK

9. Select the Red Decimal column, click the Right mouse button and choose Remove Other Columns

10. Select the Close & Load command and Press Close & Load To… – to popup Import Data dialog box.

11. In the Import Data dialog box, select the Table option, and provide the location in the  Existing worksheet to insert the transformed Power Query table into your worksheet.

Convert Hexadecimal to Decimal in Excel with VBA

VBA (Visual Basic for Applications) is used to create macros in Microsoft Office including Excel, Word, PowerPoint, and Access. You can design unique user interfaces, automate repetitive operations, capture data, and develop custom functions and formulae using these macros. VBA is a strong tool that may help you work more productively and reduce errors.

Sub HexaToDecimal()
' Declare variables
Dim rge As Range
Dim hexNum As String
Dim decNum As Long

' Assign selected range
Set rge = Sheets("VBA").Range(Selection.Address)

' loop through each cell in the range
For Each rg In rge

' Assign and initialize variable
decNum = 0
hexNum = rg.Value

' loop through each character in the hexadecimal number
For j = 1 To Len(hexNum)
    hexChar = UCase(Mid(hexNum, j, 1))

    ' check the hexadecimal character and convert to decimal number
    Select Case hexChar
        Case "A" To "F"
            decNum = decNum + ((Asc(hexChar) - 55) * (16 ^ (Len(hexNum) - j)))
        Case Else
            decNum = decNum + (Val(hexChar) * (16 ^ (Len(hexNum) - j)))
    End Select
Next

' Store decimal number to the adjacent cell
rg.Offset(0, 1).Value = decNum
Next

End Sub

VBA macro declaring the variables rge, hexNum, and decNum. Assign the selected range to the variable rge. For loop iterate each cell in the selected range, assign the current cell value to the variable hexNum and initialize decNum to 0. The second For loop, which will loop through each character in the hexNum variable.

Use the UCase() function to convert the current character to uppercase and store it in the hexChar variable.  Select Case statement will check the current character in the hexChar. If the character is between A and F, the code will use the Asc() function to convert the character to its ASCII value and subtract 55 from it. Then convert the value to a numerical value in base 10 and assign it to the decNum variable.

If the character is not between A and F, the code will convert the character to its numerical value using the Val() function and convert the number to a numerical value in base 10.  Store the decimal number in the adjacent cell.

Steps to Run the HexaToDecimal macro.

1. Select the range of cells C2:C6
2. Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
3. Choose HexaToDecimal Macro and then Press Run.

Convert Hexadecimal to Decimal in Excel with Office Scripts

Excel Office Scripts is a programming script, a superset of JavaScript and works both desktop and web. It helps you to record repetitive tasks, edit the script, create automation, and run.  It provides access to the excel application, worksheets, ranges, tables, charts, and more.

function main(workbook: ExcelScript.Workbook) {

    // getselected range
    let rng = workbook.getSelectedRange();
    let rows = rng.getRowCount();
    

    // loop through the selected range of cells
    for ( let i = 0; i < rows; i++ ) {
        let cellValue = rng.getCell( i, 0 ).getText() as string
        let decVal = 0;

        // loop through each character 
        for ( let j = 1; j <= cellValue.length; j++ ){
            let hexChar = cellValue.substr( j-1, 1 );
            hexChar = hexChar.toUpperCase();
            
            // check the hex character
            switch ( hexChar ) {          
                case "A":
                case "B":
                case "C":
                case "D":
                case "E":
                case "F":                
                    decVal = decVal + ( hexChar.charCodeAt(0) - 55) * Math.pow( 16, cellValue.length - j )  
                    break; // exit switch case statement
                default:
                    decVal = decVal + Number( hexChar ) *  Math.pow( 16, cellValue.length  - j ) 
            }
        }
        // Assign decimal value to the adjacent cell
        rng.getCell( i, 1 ).setValue( decVal );
    }
}

Script declares the variables named rng, and rows.  Use a for loop to iterate through each cell in the selected range. For each iteration, declare a variable named decVal. Assign current cell value to the variable cellValue. Declare an inner for loop to iterate through the length of the current cell value. For each iteration, declare a variable named hexChar and assign it the character at a specific index of the current cell content. Use the toUpperCase() method to convert the hexChar variable to uppercase.

Switch statement to check if the hexChar variable is equal to ‘A’, ‘B’, ‘C’, ‘D’, ‘E’ or ‘F’. If the hexChar variable is equal to any of the values, use the charCodeAt() method to get the corresponding ASCII code value for the character. Subtracting 55 from that character code converts it to a numerical value in base 10 and assigns it to the decVal variable.

If the hexChar variable is not equal to any of the A to F symbols, convert the hexChar variable to a numerical value in base 10 and assign it to the decVal variable. Store the decVal to the adjacent cell and end the for loops.

Follow the below steps to execute the HexToDec Office script in your excel

1. Select the range of cells C2:C6
2. Go to Automate menu.
3. Select the HexToDec Script – to open the Code editor on the right side of the excel window
4. Press Run 

Conclusions

The Hexadecimal system is a number system that uses a base of 16, rather than the usual 10 in the decimal system. It is used in computer science and programming.  You need to understand hexadecimal and RGB code conversion when you design your report or dashboard to align with client templates.

Now you learned to convert hexadecimal to decimal. You can use VBA or Office Scripts code in your automation to dynamically change the colors. If you are working with a large dataset, you can use the Power Query script.  HEX2DEC is a basic excel function to convert hexadecimal to decimal numbers.

Do you use any other method to Convert Hexadecimal to Decimal in Excel? Let me know in the comments section!