I need to convert space delineated hex values (ie. "B2 DD C0 B0 C8 AF C4 20 B9 DE B0 D1 81 48") in a column of cells to their SHIFT-JIS character equivalent in Excel. These strings may also include line breaks that need to be included in the translated cell value.
All of the functions and VBA code examples I've located so far appear to only work with western ascii values or unicode, which displays the incorrect characters. Converting by importing from CSV is not a viable solution, since the values are extracted from another hex dump in another worksheet (hex string extracted by using an offset table for length).
Tried using a sample VBA function to convert the hex characters, but it's not able to properly convert the SHIFT-JIS encoding.
=HexToString(SUBSTITUTE(I2,CHAR(32),"")) will result in "²ÝÀ°È¯Ä ¹Þ°ÑH" instead of "ｲﾝﾀｰﾈｯﾄ ｹﾞｰﾑ？"
Public Function HexToString(InitialString As String) As String
Dim i As Long
For i = 1 To Len(InitialString) Step 2
HexToString = HexToString & Chr("&H" & (Mid(InitialString, i, 2)))
Next i
End Function
