Converting hex to ASCII

Topic

New Reply

Is there a function or code available through which I can convert hex in A1 to ASCII in B1? In my case, the hex is expressed as a simple string, e.g., 1234AABBCC55. Thanks.

Reply | Quote

Replies

Someone may have a formula or macro, or install an add-on like ASAP. This is what I use at work.

Reply | Quote

Hello Jimmy – Will this work?

=CONCATENATE((CHAR(HEX2DEC(MID(A1,1,2)))),(CHAR(HEX2DEC(MID(A1,3,2)))),(CHAR(HEX2DEC(MID(A1,5,2)))),(CHAR(HEX2DEC(MID(A1,7,2)))),(CHAR(HEX2DEC(MID(A1,9,2)))),(CHAR(HEX2DEC(MID(A1,11,2)))))

Reply | Quote

Thanks, guys. Russ, I installed ASAP, but didn’t see a hex2text utility. Tim, your formula works to a limited extent. For example, if I have hex 6D792062726F776E20646F67 in A1, your formula in B1 returns “my bro.” Ther hex string actually is “my brown dog,” so it’s a matter of length. Perhaps I have to edit the formula for length, but I’m a little challenged by the syntax!

Reply | Quote

…. Tim, your formula works to a limited extent. For example, if I have hex 6D792062726F776E20646F67 in A1, your formula in B1 returns “my bro.” Ther hex string actually is “my brown dog,” so it’s a matter of length. Perhaps I have to edit the formula for length, but I’m a little challenged by the syntax!

This should work for 6D792062726F776E20646F67 in Cell A2 … equals “my brown dog”.

Put this in cell B2….
=CONCATENATE((CHAR(HEX2DEC(MID(A2,1,2)))),(CHAR(HEX2DEC(MID(A2,3,2)))),(CHAR(HEX2DEC(MID(A2,5,2)))),(CHAR(HEX2DEC(MID(A2,7,2)))),(CHAR(HEX2DEC(MID(A2,9,2)))),(CHAR(HEX2DEC(MID(A2,11,2)))),(CHAR(HEX2DEC(MID(A2,13,2)))),(CHAR(HEX2DEC(MID(A2,15,2)))),(CHAR(HEX2DEC(MID(A2,17,2)))),(CHAR(HEX2DEC(MID(A2,19,2)))),(CHAR(HEX2DEC(MID(A2,21,2)))),(CHAR(HEX2DEC(MID(A2,23,2)))))

Reply | Quote

The formula takes each pair of hex characters – MID(A1, 1, 2) etc – and converts them to ASCII, then concatenates the whole lot. To work with longer entries you need to add the conversion for additional pairs of characters.

cheers, Paul

Reply | Quote

Hi Jimmy – Your original example was 12 characters, so I limited the formula to 12 chars. Paul is correct about being able to modify the formula for longer characters.

You could also try this …. I got it from doing a Google search…..

Function Translate(Str As String) As String
Dim i As Integer
Dim Temp As String
If Left(Str, 2) “X'” Then
Translate = Str
Exit Function
End If
Str = Replace(Str, “X'”, “”)
Str = Left(Str, Len(Str) – 1)
For i = 1 To Len(Str) Step 2
Temp = Temp & Chr(hex2dec(Mid(Str, i, 2)))
Next i
Translate = Temp
End Function

To use it you probably need to install the Add-In Analysis ToolPak – VBA and set a reference to atpvbaen.xls in your workbook project. Once that’s done you can use it in a cell like this:

=Translate(A1)

Reply | Quote

Thanks again, Paul. I appreciate your help. First, I extended your formula, but it seems that there’s a limit. Excel presents an error if I go past (CHAR(HEX2DEC(MID(A1,23,2). I get a #Value error if I go to 25. I then installed the Analysis ToolPak – VBA add-in and pasted the code in a new module. I’m not sure what you mean by setting a reference to the atpvbaen.xls file (I see atpvbaen.xlam in the add-in window). I ran the command, but got the error in the attached screen shot, perhaps because I have not proceeded correctly. What I’d like to do is set up ny Personal.xlxb or xls with this function, so that I can use it in any workbook.

Reply | Quote

Hi Jimmy – Here is the link with additional info ….
Converting Long HEX string to ASCII

Hope that helps

Reply | Quote

Thanks, Tim. I had seen that site. I enabled the reference, but still get the “Sub or function not defined” error. I noticed that another person at that link had the same issue, but no resolution was posted. I’ll do more research.

Reply | Quote

You need to specify that you are using an Excel function. See this description.

cheers, Paul

Reply | Quote

Thanks very much, PT. I did look over the link, but not being at all conversant in VBA, I’m at a loss as to where to insert Sub UseFunction() and End Sub, let alone what, if anything, goes between. The code is posted above in my screen shot.

Reply | Quote

You need to add “Application.WorksheetFunction.” to the front of “Hex2Dec”.

Code:

Temp = Temp & Chr(Application.WorksheetFunction.hex2dec(Mid(Str, i, 2)))

cheers, Paul

Reply | Quote

Thanks, Paul. The error is gone, but the code does not work, which is not your or any other poster’s fault, as it was written by another person. If I enter 6B61746965 in A1 and =Translate(A1) in B1, the result returned is 6B61746965. It simply returns the hex value.

Reply | Quote

The function presumes that the HEX starts with an X’, Notice the line:

If Left(Str, 2) “X'” Then
Translate = Str
Exit Function
End If

Thus if it does NOT start with X’ it just returns the original….

Also note the lines:
Str = Replace(Str, “X'”, “”)
Str = Left(Str, Len(Str)-1)

If it starts with X’, it removes this and then removes the final character. If you look at the source of the code that is linked, the user had HEX of the form:
X’5374c3a97068616e65′

Also you do not need the “Application.WorksheetFunction”. You need to install the analysis toolpack and then in VB set a reference to atpvbaen.xls to run the function.

Steve

Reply | Quote

Thanks very much, Steve. Being guided through the code made everything clear. I found, however, that I still got the “Sub or function not defined” error if I went back to the original code instead of substituting Temp = Temp & Chr(Application.WorksheetFunction.hex2dec(Mid(Str, i, 2))). I had installed the analysis toolpack and set a reference to atpvbaen.xls. Now that I see how I must format my hex column, this is going to make a little extra work. I did try to edit the code to remove the “X” reference, but didn’t have much luck. I found, however, another code that seems to take a hex string without any extraneous characters:

Public Function TextToHex(Text As Range) As String
Dim i As Integer
Dim DummyStr As String

For i = 1 To Len(Text)
DummyStr = DummyStr & Right(“00” & Hex(Asc(Mid(Text, i, 1))), 2)
DoEvents
Next i

TextToHex = DummyStr
End Function

Public Function HexToText(Text As Range) As String
Dim i As Integer
Dim DummyStr As String

For i = 1 To Len(Text) Step 2
DummyStr = DummyStr & Chr(Val(“&H” & (Mid(Text, i, 2))))
DoEvents
Next i

HexToText = DummyStr

End Function

Reply | Quote