There are two good ways of converting binary to decimal. Excel has a built in function for small numbers, but you'll need a custom formula for larger ones.
Contents:
Formulas
Convert Small Binary Numbers:
= BIN2DEC(number)
number - The binary number you wish to convert to a decimal number
(Nearly) Unlimited Length Binary Number:
=LET(
binary, number,
n, LEN(binary),
sequence, SEQUENCE(1, n, n-1, -1),
SUM(--MID(binary, SEQUENCE(1, n), 1) * 2^sequence))
number - The binary number you wish to convert to a decimal number
Note: Excel has a built in limit for numerical precision. To convert binary numbers over 15 digits in length, format the cell as text or use a " ' " in front of the number
Explanation
How to Convert Large Binary Numbers to Decimal Form
The BIN2DEC function has a limit of 10 characters (or 512 maximum decimal number), if the number exceeds this, a #NUM error will be returned.
To get around this we'll need a custom formula that does the conversion math for us:
=LET(
binary, number,
n, LEN(binary),
sequence, SEQUENCE(1, n, n-1, -1),
SUM(--MID(binary, SEQUENCE(1, n), 1) * 2^sequence))
Now, if we drop this new formula along side the large binary numbers, pointing the "binary" variable to the binary numbers, the formula will convert any length binary number into decimal form:
Formula Breakdown
In case you're curious, let's go through this formula and understand how the conversion is taking place.
This formula uses the LET function which allows us to store and call variables repeatedly throughout a formula. The variables; "binary", "n", and "sequence", are all used throughout this formula.
binary, number,
This first line takes a binary number, either text or a cell reference, as an input, and stores the number as "binary".
n, LEN(binary),
This line then takes the character length of the binary number. "1100110" for example would have a length of 7.
sequence, SEQUENCE(1, n, n-1, -1),
This generates a sequence that represents the powers to which 2 must be raised, starting from n-1 down to 0.
This effectively creates an array of exponent values for each binary digit, with the highest exponent for the leftmost digit and decreasing to 0 for the rightmost digit.
This matches how binary to decimal conversion requires each digit to be multiplied by 2 raised to its corresponding position power, starting from the right at 0.
SUM(--MID(binary, SEQUENCE(1, n), 1) * 2^sequence))
This line isolates each digit of the binary string and calculates the decimal value of each binary digit by raising 2 to the power corresponding to the digit's position (as seen in the 2^sequence picture above). Effectively applying the binary place value system.
Visually this calculation looks like this:
Once that is done, all that is left is to sum up those values to obtain the decimal equivalent of the binary number.