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

[HarrySiers]

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

 

[HarrySiers]

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.

"you mean the cell you put the formula in was formatted as Text, don't you?" - Correct, and I understand/learned the importance now to describe what is visible on the screen. Thank you.

Harry

 

[shg]

You're welcome.