Convert text to hex

[stephen1]

How do I convert text to hex? A1 has "google.com". I want to convert those text characters to hexadecimal in B1.
I use a Mac, Excel 2011.
Thank you.
Stephen

 

[shg]

since I don't have a loop, what are the iterations

The formula timer code iterates each set of formulas 1, 2, 4, 8, ... times until the total time is > 2 seconds.

are you saying those one-liners were quicker than your code?

No, they are about 1/10 as fast.

 

[Rick Rothstein]

No, they are about 1/10 as fast.

Ah, that makes more sense... it is what I would have expected (not necessarily the number, just the relative result). As I said, I was bored and, at the time, it looked like trying to make them one-liners would be an interesting "challenge", which it was.

 

[stephen1]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]google.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]676F6F676C652E636F6D[/TD]
[TD]A2: =Str2Asc(A1)[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]google.com[/TD]
[TD]A3: =Asc2Str(A2)[/TD]
[/TR]
</tbody>[/TABLE]

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

Worked perfectly. Thank you!

 

[shg]

You're welcome.

 

[Rick Rothstein]

No, they are about 1/10 as fast.

I am wondering how the fast the following functions are (my gut tells me they will be faster, but my guess is by not that much)... could you do me a favor and test them out against your code using whatever measuring code/add-in you used previously?

Function Str2Asc(ByVal sInp As String) As String
  Dim X As Long
  Str2Asc = Space(2 * Len(sInp))
  For X = 1 To Len(sInp)
    Mid(Str2Asc, 2 * X - 1) = Hex(Asc(Mid(sInp, X, 1)))
  Next
End Function

Function Asc2Str(ByVal sInp As String) As String
  Dim X As Long
  Asc2Str = Space(Len(sInp) / 2)
  For X = 1 To Len(sInp) - 1 Step 2
    Mid(Asc2Str, (X + 1) / 2) = Chr("&H" & Mid(sInp, X, 2))
  Next
End Function

 

[stephen1]

I am wondering how the fast the following functions are (my gut tells me they will be faster, but my guess is by not that much)... could you do me a favor and test them out against your code using whatever measuring code/add-in you used previously?

Function Str2Asc(ByVal sInp As String) As String
  Dim X As Long
  Str2Asc = Space(2 * Len(sInp))
  For X = 1 To Len(sInp)
    Mid(Str2Asc, 2 * X - 1) = Hex(Asc(Mid(sInp, X, 1)))
  Next
End Function

Function Asc2Str(ByVal sInp As String) As String
  Dim X As Long
  Asc2Str = Space(Len(sInp) / 2)
  For X = 1 To Len(sInp) - 1 Step 2
    Mid(Asc2Str, (X + 1) / 2) = Chr("&H" & Mid(sInp, X, 2))
  Next
End Function

Hi Rick,
I took a course in stats in B school. We were programming a mainframe using SAS. The program quit running if you omitted a semicolon at the end of the "sentence." That level of minutiae ended any aspirations I had as a programmer. I even had to ask my VP of Software for a Fortune 100 to help me with the thing called a UDF to make your (kind) code work.

All this to say that I have no idea how to determine which code wins the speed race. Sorry.
It is an interesting question, though. I'd like to know the answer if someone does the test.
Stephen