# 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 **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:

**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.**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.**MOD($B7,512)**– The remainder obtained from dividing the decimal value in cell B7 by 512 is what this portion of the formula returns.**DEC2BIN(MOD($B7,512),9)**– This part of the formula converts the remainder to a binary number with 9 digits using.- The “
**&**” operator is used to concatenate the binary representations and returns the final 18-bit binary representation of the decimal number in cell B7.

https://Vavadacasino.Populiser.comI could not refraiin from commenting. Well written!

Feeel free to surf to mmy blog – https://Vavadacasino.Populiser.com