Converting Long HEX string to ASCII

jamesg

New Member
Joined
Nov 30, 2007
Messages
4
Hope someone can help.
I did an export of our users from Active Directory using CSVDE.exe. This generated a csv file which I then work with in Excel and word.
My problem is that any field that has either a carrige return, or an accent in the name come out as a long HEX string with an x' at the start and an ' at the end instead of the proper word.
For example Stephane (The e should have an accent, but I can't display that here) comes out as X'5374c3a97068616e65'.
What I need help with is a formula/macro/vba code, that would search for all cells whos data starts with x'. Once found I need to remove the x' from the begining and the ' from the end. Then pair each 2 characters of hex together and convert then to ascii. If we take the example above, X'5374c3a97068616e65' would become 5374c3a97068616e65, then 53 becomes S, 74 becomes t, c3 and a9 form e with an accent, 70 is p... and so on and so forth. Then once that's all done, put the converted name/title back into the orginal field.
I thank everyone in advance for any help they can give me with this.
If you need more information, examples, or know of a better way for me to do this, please feel free to let me know.
Thanks
James
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
This custom function is a start:

Code:
Function Translate(Str As String) As String
    Dim i As Integer
    Dim Temp As String
    If Left(Str, 2) <> "X'" Then
        Translate = Str
        Exit Function
    End If
    Str = Replace(Str, "X'", "")
    Str = Left(Str, Len(Str) - 1)
    For i = 1 To Len(Str) Step 2
        Temp = Temp & Chr(hex2dec(Mid(Str, i, 2)))
    Next i
    Translate = Temp
End Function

To use it you must install the Add-In Analysis ToolPak - VBA and set a reference to atpvbaen.xls in your workbook project. Once that's done you can use it in a cell like this:

=Translate(A1)

I could not see a relationship between c3a9 and the character é. Can you explain it? If so the function can be changed accordingly.
 
Upvote 0
I believe that c3a9 is part of the extended ascii character set. So c3 would be e and a9 would be the accent or vise versa.
 
Upvote 0
A9 in decimal is 169 which comes up as the copyright symbol for me.
 
Upvote 0
I added the add-in (I'm running Office 2007).
Now when I go into MS Visual Basic, atpvbaen.xls is listed there. When I click the plus sign it's prompting me for a password. Is this normal? I know we're not supposed to discuss passwords here, I'm simply asking if this is normal or if I did something wrong.
 
Upvote 0
Yes, it is normal because the code for that is protected. but that is not what you need to do.

Go into the VBE.
Click on Tools, then References
Check the box next to atpvbaen.xls
Click OK
 
Upvote 0
I had done that, I was just wondering if the password thing was normal. Thanks thought.
So now I do all of the above, and when I hit enter on =Translate(A1), the VBE editor comes up with an erro saying Compile error; Sub or function not defined, and it's highlighting Hex2Dec in the code above. Hex2Dec is a built in function to Excel isn't it?
 
Upvote 0
Hex2Dec is a built in function to Excel isn't it?

It's part of the Analysis Toolpak.

I would double check to see that the reference is checked.
I would also on the worksheet, under Add-ins, check Analysis Toolpak as well.
 
Upvote 0
I hope that I may bump this up, as I need to convert hex strings to ASCII, but have run into the same issue as the last poster. I get the "Sub or function not defined" error at the Hex2Dec portion of the code. I'm running Excel 2007. I installed the ToolPaks and checked the atpvbaen.xls reference. The cells with the hex strings are formatted as General. I'll be grateful for any help. Thanks.
 
Upvote 0
I hope that I may bump this up, as I need to convert hex strings to ASCII, but have run into the same issue as the last poster. I get the "Sub or function not defined" error at the Hex2Dec portion of the code. I'm running Excel 2007. I installed the ToolPaks and checked the atpvbaen.xls reference. The cells with the hex strings are formatted as General. I'll be grateful for any help. Thanks.

I came across this post while researching the same problem to convert hex to text. The information I am trying to convert is the address string from AD so the length varies. There are tools on the web that will convert for me but I need to do it in excel against a list of entries. I followed the instructions & also received the compile error message referencing hex2dec. I am in the process of reinstalling Office 2013 to see if that helps based on another post but after trying this on two different computers (one with Excel 2010 and Excel 2013); neither worked. I confirmed that both the toolpaks are installed and referenced. I assume the code listed earlier is written the right way and formatted so it can be cut and paste into excel VBA. If anyone has additional tips, I appreciate it. I am at my wits end trying to convert hex to text. This is an old one but hopefully this will help bump this thread.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
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