CODEWITHSUNDEEP
vbaexcel
Zaiem Othman
Zaiem Othman

Reputation: 83

How to convert bit number to digit

I'm working to create an Excel macro using VBA to convert bit strings to numbers. They are not binary numbers, each '1' stands for it's own number.

e.g: 1100000000000000000010001

from the left, the first bit represents "1", the second bit represents "2", third bit represents "0", and so on. The total quantity of bits in each string is 25.

I want VBA to convert it and show results like so: 1, 2, 21, 25.

I tried using Text to Columns but was not successful.

Upvotes: 1

Views: 1096

Answers (5)

pnuts
pnuts

Reputation: 59475

Another non-VBA solution, based on the OP' initial approach and with a layout designed to facilitate multiple 'conversions' (ie copy formulae down to suit):

enter image description here

Upvotes: 0

John Bustos
John Bustos

Reputation: 19564

Try this:

  Function ConvertMyRange(Rng As Range) As String

  Dim MyString As String
  MyString = Rng.Text
  Dim OutPutString As String

     For i = 1 To Len(MyString)
        If Mid(MyString, i, 1) = "1" Then OutPutString = OutPutString & ", " & i
     Next i

     ' Get rid of first ", " that was added in the loop
     If Len(OutPutString) > 0 Then
        OutPutString = Mid(OutPutString, 2)
     End If

     ConvertMyRange = OutPutString

  End Function

For your input, the output is 1, 2, 21, 25

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Public Function BitPicker(sIn As String) As String
    For i = 1 To Len(sIn)
        If Mid(sIn, i, 1) = 1 Then
            BitPicker = BitPicker & i & ","
        End If
    Next
        BitPicker = Mid(BitPicker, 1, Len(BitPicker) - 1)
End Function

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26650

Does this have to be VBA? Give a data setup like this:

Convert from bit string

The formula in cell B4 and copied down to B33 is:

=IF(ROWS(B$3:B3)>LEN($B$1)-LEN(SUBSTITUTE($B$1,"1","")),"",FIND("@",SUBSTITUTE($B$1,"1","@",ROWS(B$3:B3))))

The formula cells are formatted as General and the the Bit String cell (B1) is formatted as Text.

Upvotes: 0

Makah
Makah

Reputation: 4523

Try something like this:

Sub Execute()
    Dim buff() As String
    Dim i As Integer, total As Double

    buff = Split(StrConv(<theString>, vbUnicode), Chr$(0))
    total = 0

    For i = 0 To UBound(buff)
        Debug.Print (buff(i))
        'total = total + buff(i) * ??
    Next i
End Sub

Upvotes: 1

Related Questions