Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    JimmyW
    Helena, MT

  2. #2
    5 Star Lounger RussB's Avatar
    Join Date
    Dec 2009
    Location
    Grand Rapids, Michigan
    Posts
    803
    Thanks
    10
    Thanked 50 Times in 49 Posts
    Someone may have a formula or macro, or install an add-on like ASAP. This is what I use at work.
    Do you "Believe"? Do you vote? Please Read:
    LEARN something today so you can TEACH something tomorrow.
    DETAIL in your question promotes DETAIL in my answer.
    Dominus Vobiscum <))>(

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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. #4
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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!
    JimmyW
    Helena, MT

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,186
    Thanks
    47
    Thanked 983 Times in 913 Posts
    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. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    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. #7
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    Attached Images Attached Images
    JimmyW
    Helena, MT

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Jimmy - Here is the link with additional info ....
    Converting Long HEX string to ASCII

    Hope that helps

  9. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Jimmy-W View Post
    .... 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. #10
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    JimmyW
    Helena, MT

  11. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,186
    Thanks
    47
    Thanked 983 Times in 913 Posts
    You need to specify that you are using an Excel function. See this description.

    cheers, Paul

  12. #12
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    JimmyW
    Helena, MT

  13. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,186
    Thanks
    47
    Thanked 983 Times in 913 Posts
    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. #14
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    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.
    JimmyW
    Helena, MT

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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 LastLast

Posting Permissions

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