# Thread: Converting hex to ASCII

1. 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.

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

3. Hello Jimmy - Will this work?

=CONCATENATE((CHAR(HEX2DEC(MID(A1,1,2)))),(CHAR(HE X2DEC(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)))))

4. 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!

5. 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

6. 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)

7. 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.

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

Hope that helps

9. Originally Posted by Jimmy-W
.... 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(HE X2DEC(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(HEX2DE C(MID(A2,13,2)))),(CHAR(HEX2DEC(MID(A2,15,2)))),(C HAR(HEX2DEC(MID(A2,17,2)))),(CHAR(HEX2DEC(MID(A2,1 9,2)))),(CHAR(HEX2DEC(MID(A2,21,2)))),(CHAR(HEX2DE C(MID(A2,23,2)))))

10. 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.

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

cheers, Paul

12. 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.

13. You need to add "Application.WorksheetFunction." to the front of "Hex2Dec".
Code:
`Temp = Temp & Chr(Application.WorksheetFunction.hex2dec(Mid(Str, i, 2)))`
cheers, Paul

14. 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.

15. 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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•