bin2dec for numbers longer than 10 bits in excel

Question

I have an excel with 28 position binary numbers. I need to convert them to decimal numbers, but function bin2dec don't work with numbers longer than 10 bits. Can anyone help me with this?

Jinnyjinrikisha

Answer 1

Use the following formula to mimic a BIN2DEC function that coverts larger than 10 bits.

=SUMPRODUCT(--MID(A2,LEN(A2)+1-ROW(INDIRECT("1:"&LEN(A2))),1),(2^(ROW(INDIRECT("1:"&LEN(A2)))-1)))

Remember that Excel has a numerical precision of 15 digits. If you want 28 digits, format the cell as Text or preface the string of digits with a single tick (e.g. ') as a PrefixCharacter property.

   

Linden

Works like a charm, thanks! (btw: had to replace the "," with a ";" on my system) – Tersina

Thanks a lot. I use the german version of Excel and thus the formula has to be changed to:=SUMMENPRODUKT(--TEIL(A2;LÄNGE(A2)+1-ZEILE(INDIREKT("1:"&LÄNGE(A2)));1);(2^(ZEILE(INDIREKT("1:"&LÄNGE(A2)))-1))). Only if by chance someone also uses the German version... – Demonism

Answer 2

I brute forced it with the math, may be inelegant, but for a 16 bit number where leading 0's will be displayed this works and can easily be adapted to longer strings

This works well if you are working with fixed length words, like verifying values in memory, BIT registers, etc.

16 bit
=BIN2DEC(LEFT(R5,8))*2^8+BIN2DEC(RIGHT(R5,8))

32 bit could be 
=BIN2DEC(MID(R10,1,8))*2^24+BIN2DEC(MID(R10,9,8))*2^16+BIN2DEC(MID(R10,17,8))*2^8+BIN2DEC(MID(R10,25,8))

Again, this works if you have a fixed length input, leading zeros are displayed.

Semblance

Thank you. It is silly that BIN2DEC does not work with anything over 10 bits. – Singularize

Answer 3

Use the new spilling formulas (see this and that) to support any number up to 1.7976931348623158e+308, limited by Excel's 15 digits of precision and largest allowed numbers via formula.

Practically speaking that's:

• All integers up to 999,999,999,999,999
• 49 bits in any combination
• 50 bits as long as decimal value is less than 1 quadrillion
• 1024 bits / 309 decimal digits as long as you only care about 15 decimal digits of precision and it's less than 1.7...e+308

Copy this formula and replace A2 with whatever cell you want to convert. The formula works by extracting each digit in the string with MID, multiplying it by the appropriate power 2^x, then calculates the sum of all those individual numbers. LET is not required, but lets you substitute A2 in just one spot instead of several.

=LET(number, A2, nBitBin2Dec,LAMBDA(a,SUM(MID(a,SEQUENCE(LEN(a)),1)*2^SEQUENCE(LEN(a),1,LEN(a)-1,-1))),nBitBin2Dec(number))

Example results:

For nicer display of these numbers e.g. grouping digits into sets of ten, see my answer at https://mcmap.net/q/1476953/-how-to-add-comma-after-every-4-digits-which-length-is-1000-in-excel.

Here are the raw numbers to make copy-paste easier. Many thanks to Mobilefish Big number converter for converting the 308 digits of ="999999999999999" & CONCAT(ROUND(MAKEARRAY(293,1,LAMBDA(r,c,0)),0)) into binary for me, and similar conversions.

• 011
3
• 1011011011011011011011011101
191,739,613
• 11100011010111111010100100110001100111111111111111
999,999,999,999,999
• 1000111001100111100111000010111101011110010001001101011101111001111111111000000110000011100101100011011100010000110000000101000000010111111100101010101010000100001011100100101010111011010100010010011111011101010111111110011000100001000100100100011110100000001011010010000010111100011101001011100101101001100011110000000101110000111101011100111000111100100110000101110110110000100011001100111001000101001100110000010110100101101110011000110011101010111111100111110110001011011100101100110110000100000010111001000000110100100011111001111000011101010101100010000011100101001101111000111000101110000011011111110101001100110101111011100001011100001011101010000000110110100100100010111100100110001101111001011001111110111001100100100101100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
9.99999999999999E+307
  or without scientific notation it is 99999999999999900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
• 1111111111111111111111111111111111111111111111111111101111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
  or roughly 1.7976931348623158e+308
  or without scientific notation it is 179769313486231580793728971405303415079934132710037826936173778980444968292764750946649017977587207096330286416692887910946555547851940402630657488671505820681908902000708383676273854845817711531764475730270069855571366959622842914819860834936475292719074168444365510704342711559699508093042880177904174497791

Burkett

do you have similar for DEC2BIN? – Scribbler

@Scribbler Use the new BASE instead of DEC2BIN. For example =BASE(999999999999999,2) returns the correct result while =DEC2BIN(999999999999999) errors out with #NUM. – Burkett