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

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? 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
• 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)

• since you've done it you may as well paste the formula that does it, into the answer, it'd be helpful. 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. May 29 '14 at 17:30
• And here a picture that I made to explain it more clearly. 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))
Else
rez = rez & WorksheetFunction.Bin2Hex(Mid(arg, (lcnt * 4) - 3, 4))
End If
Next lcnt
CRY_BIN2HEX = rez
End Function
``````