In one column I have a bitstring encoded as a string type. In a different column I wish to have the same bits (potentially starting with a leading 0), but as a binary value type. Ultimately, I want to have it in hex representation (string or hex type, doesn't matter) in a third column.

How is this done? Do I have to program something for it or is there a faster way?

Input cell contains the following string: 00000100100011000100100011

I wish to obtain the string: 0123123

  • Did you try using the built-in functions?
    – and31415
    May 28 '14 at 22:07
  • I don't see anything that converts FROM a string May 28 '14 at 22:09
  • I'm not sure what you mean by a 'bitstring'. Can you please give an example of what you're trying to do? May 28 '14 at 22:13
  • "0101" of string type should be converted to 0b0101 then 0x3 May 28 '14 at 22:19
  • 1
    BIN2HEX is limited to 10 binary digits input. How many are you using? May 28 '14 at 23:06

Just split the binary number with mid(cell, index_start, len) and do a piecewise change of base with bin2hex() followed by a concatenation (via CONCATENATE() - cell references are delimited by ampersands).

Example row:

0010000100000001110100101 is in one cell X1

Split it up into ceil(len(X1)/8)=4 cells to get groups of 8 bits each.

To split it into the 4 cells, use =MID($X1,start_pos,8), where startpos is the starting index (1 based) of the bitstring in X1

In another set of 4 cells concert the previous 4 cells into hex by referencing them with =BIN2HEX(8bitNrCell,2)

Concatenate the previous 4 cells with =CONCATENATE(1stcell&2ndcell&thirdcell&fourthcell)

  • 1
    since you've done it you may as well paste the formula that does it, into the answer, it'd be helpful.
    – barlop
    May 29 '14 at 2:11
  • i saw your post just as i was doing it piece-by-piece. thanks anyways. reg the formula, i edited my answer, but i didn't do it in one step so i just list the sequence of functions i used May 29 '14 at 7:06
  • I know it was worked out in steps and not in one go, and it's beneficial to write it as you have(functionally, in steps), but now you worked it out, you have it so you could include the calculation you did. Whether it's one long cell formula (to accompany what you wrote), or a few lines of VBA, to accompany what you wrote. Then in future somebody may post a better one. And/or it would save a future person some time if they have the same problem.
    – barlop
    May 29 '14 at 17:30
  • And here a picture that I made to explain it more clearly.
    – Zertrin
    Sep 22 '14 at 13:40
Public Function CRY_BIN2HEX(arg As String) As String
  Dim rez As String
  Dim lcnt As Integer
  For lcnt = 1 To WorksheetFunction.RoundUp(Len(arg) / 4, 0)
    If lcnt = 1 Then
        rez = WorksheetFunction.Bin2Hex(Mid(arg, 1, 4))
        rez = rez & WorksheetFunction.Bin2Hex(Mid(arg, (lcnt * 4) - 3, 4))
    End If
  Next lcnt
  CRY_BIN2HEX = rez
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.