Hello and welcome back to the Excel Tip of the Week! This week, we have a General User post in which we are going to look at how to convert numbers from one representation system to another.
Excel contains several options for representing non-decimal based numbers. Excel 2010 and earlier versions can represent and use - to an extent - numbers expressed in binary, octal, and hexadecimal. Excel 2013 and later can use any base from 2 up to 36.
It's worth noting that numbers in non-decimal format are treated like text in Excel, and the usual mathematical functions such as SUM can't operate with them. Compare these two additions of a column of number expressed in decimal vs in hexadecimal:
It's clear that Excel doesn't understand that the text in the right-hand column represents numbers. If you actually need to calculate data that is stored as e.g. binary numbers, you need to first convert into decimal. This is of course quite ironic, as to actually do the computation Excel then has to convert back into binary!
Functions for converting between decimal and other bases
Excel 2010 and earlier include the following suite of functions:
=DEC2BIN(number, places)=DEC2OCT(number, places)=DEC2HEX(number, places)
Number is the decimal number, cell, or calculation to be converted.Places is an optional input that will ensure that the outputted string has a given minimum length - e.g. 37 could be represented as 00100101.
Here, the "number" is a cell with a text string that represents the number in the chosen base.
You will also note that you can't e.g. convert binary numbers into hexadecimal without an intermediate step of converting into decimal.
Excel 2013 and later versions have these more flexible functions:
=BASE(number, radix, min length)=DECIMAL(number, radix)
BASE uses a decimal number input: DECIMAL uses an appropriate text input for number.Radix is the chosen base - an integer between 2 and 36.Min length is an optional input that works like the "places" inputs for the functions discussed above.
You still have to work in decimal if you want to perform calculations on non-decimal numbers, and you still have to use decimal as a half-way house if you want to convert from one non-base-10 format to another.
One more thing
It's even rarer for it to be useful than the above functions, but Excel does also include functions for converting to and, since Excel 2013, from Roman numerals:
Both of these work with numbers up to and including 3,999.
Previous post - InkingTOTW index Next post - Sorting with formulas
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.