Multi-Lingual Special Characters shown as Character Code. How can I convert back?

[dversloot1]

Hello,

Our translation department has somehow managed to grab some of our data from the internet, paste it in excel, and have all the multilingual characters show up as their respective character codes.

Is there an easy way to convert these back to their special characters? (é, à, è.....)

An example of a cell's content is:

"Das High-Speed-HDMI®-Kabel HD3MM7MW (7 m) mit CL3-Grad für Installation in der Wand verfügt &uuml"

Any help would be greatly appreciated!

 

[pgc01]

Hi

I guess there may be many ways to do it. These are 2:

1 - write a vba snipped that replaces the html codes with the characters. For ex. replace "ü" with "ü", "Ö" with "Ö",etc.

2 - copy the text to a text file. Open it with a browser and you'll see all your special characters displayed. You then just have to select the text in the browser and copy it back to excel.

P. S. Kind of incoherent the text you posted. You've got the u with Umlaut in für and verfügt, but the code at the end. Why are they not all displayed as ü or as the respective code?

 

[pgc01]

Sorry, the html ate part of point 1. Should be:


1 - write a <acronym title="visual basic for applications">vba</acronym> snippet that replaces the html codes with the characters. For ex. replace "&uuml;" with "ü", "&Ouml;" with "Ö",etc.

 

[dversloot1]

This is what I came up with to perform a search and replace macro:

Sub UnescapeCharacters()


Application.ScreenUpdating = False


' set this to match your case
sheetname = ActiveSheet.Name


Dim sheet As Worksheet
Set sheet = Worksheets(sheetname)


For Row = 1 To sheet.UsedRange.Rows.Count
For Column = 1 To sheet.UsedRange.Columns.Count
Dim cell As Range
Set cell = sheet.Cells(Row, Column)


ReplaceCharacter cell, "&", "&"
ReplaceCharacter cell, "&apos;", "'"
ReplaceCharacter cell, "Œ", "Œ"
ReplaceCharacter cell, "œ", "œ"
ReplaceCharacter cell, "Š", "Š"
ReplaceCharacter cell, "š", "š"
'This list goes on

Application.ScreenUpdating = True

Next Column
Next Row


End Sub


Sub ReplaceCharacter(ByRef cell As Range, ByVal find As String, ByVal replacement As String)


Dim result As String
cell.Value = Replace(cell.Text, find, replacement, 1, -1)


End Sub

 

[dversloot1]

I just noticed that the HTML Entity that I wanted to replace, showed up as the actual special character....
Please note that the "&" and all those in the following lines are written as HTML in my vba. - "& a m p ; " <- minus the spaces