Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: From text to ASCII (hex) and from ASCII (hex) to text

  1. #1
    Board Regular
    Join Date
    May 2015
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default From text to ASCII (hex) and from ASCII (hex) to text

    I have a named table called TEXT2ASCIIHEX consisting of the following letters and their ASCII (hex) codes:

    a 0061
    b 0062
    c 0063
    d 0064
    e 0065
    f 0066
    g 0067
    h 0068
    i 0069
    j 006A
    k 006B
    l 006C
    m 006D
    n 006E
    o 006F
    p 0070
    q 0071
    r 0072
    s 0073
    t 0074
    u 0075
    v 0076
    w 0077
    x 0078
    y 0079
    z 007A


    I have a spreadsheet with the following words in cells A1,B1,C1,D1,E1,F1,G1,H1,I1:

    A1: the
    B1: quick
    C1: brown
    D1: fox
    E1: jumps
    F1: over
    G1: the
    H1: lazy
    I1: dog

    I am looking for VBA script which would copy the text content in cells A1 through I1 and place it into cells A2 through I2 *in ASCII (hex)*, which would lead to the following output:

    A2: 007400680065
    B2: 0071007500690063006B
    C2: 00620072006F0077006E
    D2: 0066006F0078
    E2: 006A0075006D00700073
    F2: 006F007600650072
    G2: 007400680065
    H2: 006C0061007A0079
    I2: 0064006F0067

    As soon as the content in cells A1 through I1 is changed, the script should change the content in row 2 as well. If A1 changes from 'the' to 'a', A2 should change from 007400680065 to 0061

    Furthermore, I am looking for VBA-script that does the opposite, from ASCII (hex) to text:

    In A1 I have the code string 00680065006C006C006F, in B1 I have the code string 0067006F006F0064006200790065

    I am looking for VBA-script which would copy the ASCII (hex) code content in cells A1 and B1 and place it into cells A2 and B2 *in text*, which would lead to the following output:

    A2: hello
    B2: goodbye

    As soon as the content in cells A1 and B1 is changed, the script should change the content in row 2 as well. If A1 changes from '00680065006C006C006F ' to '0067006F006F0064006200790065', B2 should change from 'hello' to 'goodbye'

    Is it possible?

    Thank you in advance for your help,
    Harry

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    10 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Row\Col
    A
    B
    C
    D
    1
    The 546865 The B1: =Str2Asc(A1)
    2
    quick 717569636B quick C1: =Asc2Str(B1)
    3
    brown 62726F776E brown


    Code:
    Function Str2Asc(ByVal sInp As String) As String
        Dim i           As Long
    
        For i = 1 To Len(sInp)
            Str2Asc = Str2Asc & Right$("0" & Hex$(Asc(Mid$(sInp, i, 1))), 2)
        Next i
    End Function
    
    Function Asc2Str(ByVal sInp As String) As String
        Dim i           As Long
    
        For i = 1 To Len(sInp) Step 2
            Asc2Str = Asc2Str & Chr$("&H" & Mid$(sInp, i, 2))
        Next i
    End Function

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Give these two UDFs (user defined functions) a try...

    Code:
    Function Text2Hex(S As String) As String
      Dim X As Long
      For X = 1 To Len(S)
        Text2Hex = Text2Hex & Format(Hex(Asc(Mid(S, X, 1))), "@@@@")
      Next
      Text2Hex = Replace(Text2Hex, " ", 0)
    End Function
    
    Function Hex2Text(S As String) As String
      Dim X As Long
      For X = 1 To Len(S) Step 4
        Hex2Text = Hex2Text & Chr("&h" & Mid(S, X, 4))
      Next
    End Function

    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Text2Hex and Hex2Text just like they were built-in Excel functions. For example,

    =Text2Hex(A1)

    =Hex2Text(A2)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    Board Regular
    Join Date
    May 2015
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Quote Originally Posted by shg View Post
    Row\Col
    A
    B
    C
    D
    1
    The 546865 The B1: =Str2Asc(A1)
    2
    quick 717569636B quick C1: =Asc2Str(B1)
    3
    brown 62726F776E brown


    Code:
    Function Str2Asc(ByVal sInp As String) As String
        Dim i           As Long
    
        For i = 1 To Len(sInp)
            Str2Asc = Str2Asc & Right$("0" & Hex$(Asc(Mid$(sInp, i, 1))), 2)
        Next i
    End Function
    
    Function Asc2Str(ByVal sInp As String) As String
        Dim i           As Long
    
        For i = 1 To Len(sInp) Step 2
            Asc2Str = Asc2Str & Chr$("&H" & Mid$(sInp, i, 2))
        Next i
    End Function
    Thank you very much, shg, for your quick reaction!

    Harry

  5. #5
    Board Regular
    Join Date
    May 2015
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Quote Originally Posted by Rick Rothstein View Post
    Give these two UDFs (user defined functions) a try...

    Code:
    Function Text2Hex(S As String) As String
      Dim X As Long
      For X = 1 To Len(S)
        Text2Hex = Text2Hex & Format(Hex(Asc(Mid(S, X, 1))), "@@@@")
      Next
      Text2Hex = Replace(Text2Hex, " ", 0)
    End Function
    
    Function Hex2Text(S As String) As String
      Dim X As Long
      For X = 1 To Len(S) Step 4
        Hex2Text = Hex2Text & Chr("&h" & Mid(S, X, 4))
      Next
    End Function

    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Text2Hex and Hex2Text just like they were built-in Excel functions. For example,

    =Text2Hex(A1)

    =Hex2Text(A2)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Thank you very much! I was wondering why the formula =Text2Hex(A1) works fine if I insert it in a row outside the column area A-I, but not, for instance, if I insert the formula in cell A10? My knowledge is not sufficient to figure it out, I am sure the answer is simple and logical.

    Harry

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Quote Originally Posted by HarrySiers View Post
    I was wondering why the formula =Text2Hex(A1) works fine if I insert it in a row outside the column area A-I, but not, for instance, if I insert the formula in cell A10?
    The UDFs that I posted work fine for me no matter where I put them... there are no restrictions as to where they can be placed (except they cannot be placed in the cell with the text, of course).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    May 2015
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Quote Originally Posted by Rick Rothstein View Post
    The UDFs that I posted work fine for me no matter where I put them... there are no restrictions as to where they can be placed (except they cannot be placed in the cell with the text, of course).
    I found out why the error occurred in my sheet. The cells in A-I were not formatted as General. Problem solved. Thank you again.

    Harry

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Quote Originally Posted by HarrySiers View Post
    I found out why the error occurred in my sheet. The cells in A-I were not formatted as General.
    I am curious... what were they formatted as that caused the problem?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular
    Join Date
    May 2015
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Quote Originally Posted by Rick Rothstein View Post
    I am curious... what were they formatted as that caused the problem?
    Text

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,903
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: From text to ASCII (hex) and from ASCII (hex) to text

    Quote Originally Posted by HarrySiers View Post
    Text
    The cell containing the argument can be formatted as Text and my functions will still work. Oh, wait a minute... you mean the cell you put the formula in was formatted as Text, don't you? If so, next time you describe it, don't say the UDF doesn't work, say that you see the actual formula, equal sign and all... that would have told us what the problem was immediately.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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