VBA Hex to Text

Purpose: This project came from a need to read thousands of lines of text at work being stored on a database in hexadecimal. The text was being stored this way because it contains a lot of punctuation that could cause databases to think it is delimiters (commas, semi colons, etc.). After only finding online converters that did one string at a time and being surprised I did not find a simple way to convert an entire string in Excel at once. I decided a quick VBA script would be the quickest way to complete this task and would allow me to reuse the work if I ever needed it. In Excel, I figured out how to convert two hex characters into one readable character at a time so this code simply loops through the string, 2 digits at a time and creates the text string.

Here is the VBA code for this function:

Function HexToText(CellRef As String) As Variant
Dim i As Long
Dim OpText As String
Dim TextSection As String
Dim Text As String

For i = 1 To Len(CellRef) / 2
TextSection = Mid(CellRef, (i * 2) - 1, 2)
Text = Chr(WorksheetFunction.Hex2Dec(TextSection))
OpText = OpText & Text 'Concat Letters to form optext
Next i

HexToText = OpText

End Function