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

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

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.

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).

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:

Formulas:

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

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

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

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)

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 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.

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)

…

BinDecReverse(

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

…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.

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