How to convert a string to a binary, then hex, value?

Question

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

Answer 1

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)

Answer 2

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))
    Else
        rez = rez & WorksheetFunction.Bin2Hex(Mid(arg, (lcnt * 4) - 3, 4))
    End If
  Next lcnt
  CRY_BIN2HEX = rez
End Function