Convert code to text UDF

Question

430061006E0061006400610000

I occasionally come across strings like this, where alternate pairs (1st, 3rd etc.) of characters give the hex code of an ASCII character. The remaining character pairs are all null. This type of code must have a name (it's used a lot in the registry, for example), but I don't know what it is.

Before I waste several evenings trying to find out how to automate converting this to text, does anyone have an elegant little UDF up their sleeve to achieve this? I currently have a worksheet with many such (very long) strings in a column. What I'm aiming for is a result like this:

[Posted to Other/Unknown because I don't expect any answer to be version specific and I have both 2003 and 2013 operative at the moment]

Answer 1

It's a hexadecimal string representing Unicode text. In Unicode, each character takes up 2 bytes; for standard ASCII/ANSI characters, the second byte is 0.

Try this function:

Function CODE2TEXT(s As String) As String

    Dim i As Long

    Dim n1 As Long

    Dim n2 As Long

    Dim n As Long

    For i = 1 To Len(s) Step 4

        n1 = Application.WorksheetFunction.Hex2Dec(Mid(s, i, 2))

        n2 = Application.WorksheetFunction.Hex2Dec(Mid(s, i + 2, 2))

        n = 256 * n2 + n1

        CODE2TEXT = CODE2TEXT & ChrW(n)

    Next i

End Function

If you store this function in a module in the workbook where you use it, syntax is

=CODE2TEXT(A2)

If you store it in your Personal.xls (or Personal.xlsb) workbook, the syntax is

=Personal.xls!CODE2TEXT(A2)

For Excel 2007 and later, use Personal.xlsb.

Answer 2

Intel processors reverse the order of bytes: the "high-value" byte comes after the "low-value" byte.

I'm afraid I don't know anything about ESE databases.

Answer 3

Brilliant - thank you so much. I hadn't recognized the Unicode connection because I would have expected, say, C to be represented as 0043 and not 4300. I'm still not quite sure how it works.

Anyway, I can now continue with my main task, which is to extract a particular set of data from an ESE database and convert it to a text file for use in another context. At the moment, I'm using a third-party utility to extract the hex data, but it would be wonderful if I could achieve this using only Excel, or perhaps Excel + native Windows functions. You wouldn't have any ideas, would you?