Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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!

share|improve this answer
    
Thanks a lot for the great effort! I'm actually looking for a solution without programming, like some internal Excel feature, because the user of the software is quite far from being a computer expert. –  Adam Matan Dec 2 '11 at 16:20
    
My background means I almost always look for a programming solution to any problem. This can be a mistake because Excel does have some amazing non-programming functionality. Once I had noticed that the strings were the same length, the investigation was quite easy. Perhaps this information will help someone offer another approach. It may help if you identify the scale of the problem. Are we talking about a relatively small number of column headings or text values on every one of 30,000 rows? Either way I do not see this as difficult programming problem. Perhaps a expert friend would help. –  Tony Dallimore Dec 2 '11 at 17:49
    
@AdamMatan. I am curious. Did anything come of this? –  Tony Dallimore Jan 5 '12 at 0:35
    
Unfortunately, no. I had to fix a workaround by exporting the data as text, and manually importing to Excel. –  Adam Matan Jan 6 '12 at 14:34
add comment
  1. Save the file to .csv comma-delimited format.
  2. Open it in OpenOffice, it'll let you change the encoding to the proper one.
  3. While in OpenOffice, save the file back to .xls format.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.