Change excel encoding

Question

Some ill-written program has exported an Excel file with the wrong Hebrew encoding, with cryptic strings like:

ãåç ì÷åçåú äåãòåú åéîé äåìãú

Using an encoding-solver site, I found out that the original text was:

דוח לקוחות הודעות וימי הולדת 

Which, by the way, means "Client report-notifications and birthdays", in Hebrew.

Is there a way to change the encoding for the entire document from within Excel?

Answer 1

This is not a complete solution but it looks to be a step towards the solution.

The two strings are both 28 characters long which suggests a one-to-one encoding.

Displaying the two string in hexadecimal gives:

(E3)(E5)(E7)(20)(EC)(F7)(E5)(E7)(E5)(FA)(20)(E4)(E5)(E3)(F2)(E5)(FA)(20)(E5)(E9)(EE)(E9)(20)(E4)(E5)(EC)(E3)(FA)
(5D3)(5D5)(5D7)(20)(5DC)(5E7)(5D5)(5D7)(5D5)(5EA)(20)(5D4)(5D5)(5D3)(5E2)(5D5)(5EA)(20)(5D5)(5D9)(5DE)(5D9)(20)(5D4)(5D5)(5DC)(5D3)(5EA)

The second line is mainly the Unicode values for Hebrew letters as expected.

The conversion to get the first row appears to be:

  20 (space) unchanged
  5Dx        Ex
  5Ex        Fx

This is not a complete conversion chart because the Unicode values for the Hebrew letters extend from hexadecimal 591 to 5F4.

The following crude routine accepts the first string and returns the second.

Function Decode(CompactStg As String) As String

  Dim CompactChar As Integer
  Dim DecodedChar As Integer
  Dim DecodedStg As String
  Dim Pos As Integer

  DecodedStg = ""

  For Pos = 1 To Len(CompactStg)
    CompactChar = Asc(Mid(CompactStg, Pos, 1))
    Select Case CompactChar
      Case 32 To 127     ' Hex 20 - 7F
        ' No change
      DecodedChar = CompactChar
      Case 224 To 239    ' Hex E0 - EF
        ' Convert EX to 5DX
        DecodedChar = CompactChar + 1264
      Case 240 To 255    ' Hex F0 - FF
        ' Convert FX to 5EX
        DecodedChar = CompactChar + 1264
    End Select
    DecodedStg = DecodedStg & ChrW(DecodedChar)
  Next

  Decode = DecodedStg

End Function

I used the following routine to output the strings to the immediate window. With a few more examples you will be able to determine all the conversions. Hebrew letters hexadecimal 591 to 5C7 may also be converted by adding decimal 1264 but this cannot be the conversion for Hebrew letters 5F0 to 5F4.

Sub HexOut(Stg)

  Dim Pos

  For Pos = 1 To Len(Stg)
    Debug.Print "(" & Hex(AscW(Mid(Stg, Pos, 1))) & ")";
  Next
  Debug.Print

End Sub

Have fun!