# Binary to Decimal Conversion

An emailer has a 16 digit binary string (1’s and 0’s) and a 17th bit indicating whether the number is positive or negative. He read the Decimal to Binary post and all the great comments, but wants to do the reverse. That is, he wants a UDF to convert the binary string to a decimal number. And he wants to convert negative numbers assuming two’s complement notation.

Function BinToDec(sBinary As String, sSign As String) As Long

Dim i As Long
Dim lReturn As Long
Dim lBit As Long

Const sNEGATIVE As String = “1”
Const sOFF As String = “0”

For i = 1 To Len(sBinary)
If sSign = sNEGATIVE Then
If Mid\$(sBinary, i, 1) = sOFF Then
lBit = 1
Else
lBit = 0
End If
Else
lBit = Val(Mid\$(sBinary, i, 1))
End If
lReturn = lReturn + (lBit * (2 ^ (Len(sBinary) – i)))
Next i

If sSign = sNEGATIVE Then
BinToDec = -(lReturn + 1)
Else
BinToDec = lReturn
End If

End Function

To test it, I broke out the bits manually and added them up

Posted in Uncategorized

## 10 thoughts on “Binary to Decimal Conversion”

1. fzz says:

You’re checking the value of sSign on every iteration of the For loop when it doesn’t change. And you’re not trapping easy-to-catch errors such as passing “” as sBinary or having anything other than 0 or 1 in sBinary. Poor coding.

Also, if the binary values are signed using 2s complement, then the sign bit would be the first bit. However, the argument order for your function would require something like =BinToDec(RIGHT(x,n-1),LEFT(x,1)). Seems a bit awkward.

An alternative would be passing the entire binary value and making the maximum length an optional argument with the default set in the VBA module.

Private Const DEFAULT_BINARY_DIGITS As Long = 8  ‘includes leading sign bit

Function b2d(b As String, Optional dgts As Long = DEFAULT_BINARY_DIGITS) As Variant
Dim i As Long, j As Long, k As Long

b = Application.WorksheetFunction.Trim(b)
k = Len(b)

If k = 0 Or k > dgts Or b Like “*[!01]*” Then
b2d = CVErr(xlValue)
Exit Function
End If

j = 1
For i = k To 1 Step -1
If Mid\$(b, i, 1) = “1” Then b2d = b2d + j
j = 2 * j
Next i

If k = dgts And Left\$(b, 1) = “1” Then b2d = -(2 ^ dgts – b2d)

End Function

A variation on this would be to allow Len(b) to exceed dgts, in which case dgts would be set to Len(b).

2. The interesting question is how does one deal with a number that requires a temporary number larger than the largest value that can be stored in the variable.

In this case, using a Long variable restricts the largest value that can be stored to 2^31 – 1. So, b2d fails when dealing with any number with 31 or 32 bits in it — the j=2*j inside the i loop fails — and BinToDec has a problem with any 32-bit negative number — the 2 ^ (Len(sBinary) — fails. [Of course, DK did write that his solution was for 16 bit numbers with an independent 17th sign bit.]

The code below works — with the limited testing I did — for a range of 32 bit numbers.

Values:

A B C D E
4 Val BinToDec b2d VBABin2Dec LEN()
5 1000 -8 -8 -8 4
6 1 1 1 1 1
7 1111111 127 127 127 7
8 1000000000 -512 -512 -512 10
9 01111111111111111111 524287 524287 524287 20
10 1111 15 15 15 4
11 11111111111111111111111111111111 #VALUE! #VALUE! -1 32
12 01111111111111111111111111111111 2147483647 #VALUE! 2147483647 32
13 10000000000000000000000000000000 #VALUE! #VALUE! -2147483648 32
14 0000000000000000000000000000000 0 #VALUE! 0 31
15 1 -1 -1 -1 1
16 10 -2 -2 -2 2
17 11 -1 -1 -1 2
18 1 1 1 1 1

Formulas:

A B C D E
4 Val BinToDec b2d VBABin2Dec LEN()
5 1000 =BinToDec(A5,1) =b2d(A5,4) =VBABin2Dec(A5,4) =LEN(A5)
6 1 =BinToDec(A6,0) =b2d(A6,4) =VBABin2Dec(A6,4) =LEN(A6)
7 1111111 =BinToDec(A7,0) =b2d(A7,8) =VBABin2Dec(A7,8) =LEN(A7)
8 1000000000 =BinToDec(A8,1) =b2d(A8,LEN(A8)) =VBABin2Dec(A8,LEN(A8)) =LEN(A8)
9 01111111111111111111 =BinToDec(A9,0) =b2d(A9,LEN(A9)) =VBABin2Dec(A9,LEN(A9)) =LEN(A9)
10 1111 =BinToDec(A10,0) =b2d(A10,8) =VBABin2Dec(A10,8) =LEN(A10)
11 11111111111111111111111111111111 =BinToDec(A11,0) =b2d(A11,32) =VBABin2Dec(A11,32) =LEN(A11)
12 01111111111111111111111111111111 =BinToDec(A12,0) =b2d(A12,32) =VBABin2Dec(A12,32) =LEN(A12)
13 10000000000000000000000000000000 =BinToDec(A13,0) =b2d(A13,32) =VBABin2Dec(A13,32) =LEN(A13)
14 0000000000000000000000000000000 =BinToDec(A14,0) =b2d(A14,32) =VBABin2Dec(A14,32) =LEN(A14)
15 1 =BinToDec(A15,1) =b2d(A15,1) =VBABin2Dec(A15,1) =LEN(A15)
16 10 =BinToDec(A16,1) =b2d(A16,2) =VBABin2Dec(A16,2) =LEN(A16)
17 11 =BinToDec(A17,1) =b2d(A17,2) =VBABin2Dec(A17,2) =LEN(A17)
18 1 =BinToDec(A18,0) =b2d(A18,2) =VBABin2Dec(A18,2) =LEN(A18)
Option Explicit

Private Const DEFAULT_BINARY_DIGITS As Long = 8  ‘includes leading sign bit

Function VBABin2Dec(ByVal BinStr As String, _
Optional ByVal NbrDig As Long = DEFAULT_BINARY_DIGITS) As Variant
BinStr = Trim(BinStr)

Dim strLen As Integer
strLen = Len(BinStr)
If strLen = 0 Or strLen > NbrDig Or BinStr Like “*[!01]*” Then
VBABin2Dec = CVErr(xlValue) ‘Could provide more detailed errors
Exit Function
End If

Dim Pow As Long: Pow = 1
Dim I As Integer
For I = strLen To 3 Step -1
If Mid\$(BinStr, I, 1) = “1” Then VBABin2Dec = VBABin2Dec + Pow
Pow = 2 * Pow
Next I

If Mid\$(BinStr, 2, 1) = “1” Then VBABin2Dec = VBABin2Dec + Pow

If strLen = NbrDig Then Pow = -Pow

If Left\$(BinStr, 1) = “1” Then _
VBABin2Dec = VBABin2Dec + Pow + IIf(strLen = 1, 0, Pow)

End Function

3. Hui... says:

If I put a binary number in B8: of 1001100011100010
I can use =SUMPRODUCT((POWER(2,(ROW(INDIRECT(“1:”&LEN(B8)))-1))*MID(B8,LEN(B8)-ROW(INDIRECT(“1:”&(LEN(B8))))+1,1)))
Which returns 39,138

4. Hui... says:

and if I put as Text in B9: ‘11001100011100010
I can extend the previous formula to allow for negatives to:
=IF(LEFT(B9,1)=”1?,-1,1)*SUMPRODUCT((POWER(2,ROW(INDIRECT(“1:”&(LEN(B9)-1)))-1)*MID(RIGHT(B9,LEN(B9)-1),LEN(B9)-ROW(INDIRECT(“1:”&(LEN(B9)-1))),1)))
Which now returns -39,138

5. Hui,
In 2s complement notation, the most significant bit (MSB) carries “weight”, i.e., is worth -2^(m-1) where it is the m-th digit. It’s not just a sign bit. So, if my arithmetic is correct, 11001100011100010 is -26398 and 01001100011100010 is 39138.

I used the formula =-LEFT(A11,1)*2^(LEN(A11)-1)+IF(LEN(A11)>1,SUMPRODUCT(2^(LEN(A11)-ROW(INDIRECT(“2:”&LEN(A11)))),MID(A11,ROW(INDIRECT(“2:”&LEN(A11))),1)),0)

6. fzz says:

OK, I was lazy and didn’t consider 31- or 32-bit integers. The following modification handles any integers which can be fully represented by Excel (1 sign bit + 49 mantissa bits for 15 decimal numerals = 50 bits in total). And I’m now copying Dick’s approach to accumulating negatives.

Private Const DEFAULT_BINARY_DIGITS As Long = 17  ‘includes leading sign bit

Private Const MAX_XLDP_BITS As Long = 50            ‘includes sign bit

Function b2d( _

b As String, _

Optional dgts As Long = DEFAULT_BINARY_DIGITS _

) As Variant

‘———————————————–

Dim i As Long, j As Double, k As Long, s As String * 1

b = Application.WorksheetFunction.Trim(b)

k = Len(b)

If dgts > MAX_DP_BITS Or k = 0 Or k > dgts Or b Like “*[!01]*” Then

b2d = CVErr(xlValue)

Exit Function

End If

j = 1#

s = IIf(k = dgts And Left\$(b, 1) = “1”, “0”, “1”)

For i = k To 1 Step -1

If Mid\$(b, i, 1) = s Then b2d = b2d + j

j = 2# * j

Next i

If s = “0” Then b2d = -b2d – 1

End Function

No special cases for the last few bits. Cost: 4 additional bits of memory for changing j’s type from Long to Double.

7. BinToDec(
Text: =SUM(MID(T4;ROW(INDIRECT(“2:”&LEN(T4)));1)*2^ROW(INDIRECT(“1:”&(LEN(T4)-1)))*-1^LEFT(T4;1))
Numbers: =SUM(C13:S13*2^(COLUMN(\$S\$13)-COLUMN(C13:\$S\$13))*-1^\$B\$13)

8. BinDecReverse(
=SUMPRODUCT(MID(F14,ROW(INDIRECT(“2:”&LEN(F14))),1)*2^-(ROW(INDIRECT(“2:”&LEN(F14)))-LEN(F14))*-1^LEFT(F14,1))

9. …note that =CODE(“) is 148 and =CODE(“) is 34.
In Excel they look exactly the same. The wrong ones are slightly leaning, in the posts.
Replace all ” with ” and the formulas above will work.

10. Jim says:

Try =(-LEFT(G19,1)*2^(LEN(G19)-1)+BIN2DEC(RIGHT(G19,(LEN(G19)-1))))

Posting code? Use <pre> tags for VBA and <code> tags for inline.