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

HarrySiers

Board Regular
Joined
May 27, 2015
Messages
61
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

<tbody>
</tbody>


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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Row\Col
A​
B​
C​
D​
1​
The546865TheB1: =Str2Asc(A1)
2​
quick717569636BquickC1: =Asc2Str(B1)
3​
brown62726F776Ebrown

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
 
Upvote 0
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.
 
Upvote 0
Row\Col
A​
B​
C​
D​
1​
The546865TheB1: =Str2Asc(A1)
2​
quick717569636BquickC1: =Asc2Str(B1)
3​
brown62726F776Ebrown

<tbody>
</tbody>


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
 
Upvote 0
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
 
Upvote 0
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?
:confused: 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).
 
Upvote 0
:confused: 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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top