Convert Large Numbers ( More than 10 Bits) from Decimal to Binary and Vice Versa in Excel

The BIN2DEC and DEC2BIN functions in Excel are used to convert binary and decimal numbers to their corresponding binary or decimal equivalents. They do, however, have some limitations: the DEC2BIN function only supports from -512 up to 511 decimal integers, while the BIN2DEC function only supports up to 10 bits. You might need to use different techniques or outside solutions for conversion needs that are more advanced.

Converting Large Binary Numbers to Decimal

Converting 16 Bit binary Number to Decimal by BIN2DEC Function

With the following formula, 16-bit binary numbers can be converted to equivalent decimal numbers.

=BIN2DEC(LEFT(B3,8))*2^8+BIN2DEC(RIGHT(B3,8))

Note: As we are using the LEFT and RIGHT functions, the input value must be in text format.

Explanation:

• LEFT(B3,8) retrieves the leftmost 8 characters of the  binary number specified in cell B3 
• The function BIN2DEC(LEFT(B3,8)) converts the leftmost 8 bits to their decimal equivalent.
• These bits are effectively moved eight places to the left by multiplying by 2^8.
• The BIN2DEC function converts the rightmost 8 bits to their decimal equivalent in BIN2DEC(RIGHT(B3,8)).
• The formula BIN2DEC(LEFT(B3,8))*256+BIN2DEC(RIGHT(B3,8)) adds the leftmost 8 bits decimal equivalent to the rightmost 8 bits decimal equivalent. This provides the complete 16 bits binary number’s decimal equivalent.

Converting 32 Bit binary Number to Decimal by BIN2DEC Function

Similarly, 32 bits binary numbers can be converted to equivalent decimal numbers using the following formula

=BIN2DEC(MID(B6,1,8))*2^24+BIN2DEC(MID(B6,9,8))*2^16+BIN2DEC(MID(B6,17,8))*2^8+BIN2DEC(MID(B6,25,8))

Note: Input value must be in text format

Explanation:

• MID(B6,1,8) retrieves the first 8 characters (or bits) from the binary integer defined in cell B6, starting with character 1.
• The BIN2DEC function converts the first 8 bits to their decimal equivalent in BIN2DEC(MID(B6,1,8)) and multiplies by 2^24 to move those bits 24 places to the left.
• Beginning with the ninth letter, MID(B6,9,8) retrieves the second 8 bits of the binary number provided in cell B6.
• The second 8 bits are converted to their decimal equivalent by BIN2DEC(MID(B6,9,8)) and multiplied by 2^16.
• Same process runs for remaining binary bits by BIN2DEC(MID(B6,17,8))*2^8 and BIN2DEC(MID(B6,25,8))
• Finally, all bits are added together to yield the equivalent decimal number for the total 32-bit binary.

Converting Large Decimal Numbers to Binary

Converting Negative Signed Decimal Large Numbers to Binary by BASE Function

For converting between several number systems, including binary, decimal, and hexadecimal, Excel’s BASE function is used.

Syntax of BASE Function

=BASE(number, radix, [minimum_length])

Argument of BASE Function

number: The number you want to convert.

radix: the base of the number system that you want to convert the number to. Any number between 2 and 36 may be used here.

minimum_length (optional): The output string’s minimum length (maximum  255 can be added). Leading zeros will be added to the output string if the converted number has fewer digits than the provided minimum length.

Converting Negative Decimal Numbers by Changing Sign Bit of Equivalent Binary Number 

Now let’s apply the following formula to convert large signed decimal numbers to binary.

=SWITCH(LEFT(BASE(ABS(B3),2),1)=0,1,LEFT(BASE(ABS(B3),2),1)=1,0)&MID(BASE(ABS(B3),2),2,63)

Explanation: 

• The ABS(E4) function returns the absolute value of the decimal number. 
• BASE(ABS(E4), 2): Base-2 (binary) representation of the number in cell E4’s absolute value is returned by this function.
• (LEFT(BASE(ABS(B3),2),1)): The Left function extracts the first bit from the total output of the BASE function.
• SWITCH(LEFT(BASE(ABS(B3),2),1)=0,1,LEFT(BASE(ABS(B3),2),1)=1,0) this portion of the formula alters the sign bit as we are dealing with negative numbers.
• MID(BASE(ABS(E4), 2), 2, 63): This function returns the second through sixty-four binary characters.
• The & operator is used to combine the two components of the formula. The outcome is a string that represents the number in cell E4’s binary representation.

Converting Signed Decimal Large Numbers to Binary (16-bit 2’s Compliment) in Excel

The formula converts any negative numbers to binary numbers by adding 2^16 to get the equivalent 2’s complement of the number. This formula is applicable to decimal numbers having up to five digits. 

=BASE(IF(B6<0,B6+2^16,B6),2)

Explanation:

• Whether the value in cell B6 is smaller than zero is determined using the IF function. The formula added (actually subtracted as we are entering negative numbers in cell B6)  the value in cell B6 by 2^16 if it is less than zero. and converted into two’s complement notation.
• The formula utilizes the initial value of cell B6 if the value there is not zero.
• The BASE function converts the number into binary.

Converting Unsigned Decimal Large Numbers to Binary (32-bit 2’s Compliment) in Excel

Similarly, you can apply the following formula to convert the negative numbers into 32-bit binary (2’s complement). This formula is applicable to decimal numbers with up to 10 digits. 

=BASE(IF(B9<0,B9+2^32,B9),2)

Explanation: 

Please follow the explanation for 16bit 2’s complement of decimal numbers.

Converting Unsigned Decimal Large Numbers to Binary by DEC2BIN Function

The following formula converts a decimal number (Zero to 2^32-1) from cell B7 to binary (18 bits), separating the decimal number into two parts, the first 9 of which stand for the integer portion and the remaining 9 for the fractional portion.

=DEC2BIN(INT($B7/512),9)&DEC2BIN(MOD($B7,512),9)

Note: The value in cell B7 is divided by 512 as the BIN2DEC function can hold upto 512

Explanation: 

1. INT($B7/512) – This part of the formula takes the integer part of the decimal number in cell B2 when divided by 512. The INT function rounds down the result to the nearest integer and gives us the integer part of the binary number represented by the first 9 bits.
2. DEC2BIN(INT($B7/512),9) – Using the DEC2BIN function, this component of the formula transforms the integer portion into a binary number with 9 digits.
3. MOD($B7,512) – The remainder obtained from dividing the decimal value in cell B7 by 512 is what this portion of the formula returns.
4. DEC2BIN(MOD($B7,512),9) – This part of the formula converts the remainder to a binary number with 9 digits using.
5. The “&” operator is used to concatenate the binary representations and returns the final 18-bit binary representation of the decimal number in cell B7.