find and replace all special characters (after an encoding problem)

Question

find and replace all special characters (after an encoding problem)

Anonymous

Hello,

I created a huge Excel file with text.

Unfortunately when the data was retrieved, there was an encoding problem.

Some special characters like "é" were replaced by "Ã©".

I would like to find all "Ã©" and replace them by "é"

with CTRL+F it worked well in Office 2010 but it does not work any more.

How can I do?

Regards,

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Answer 1

Hello,

Many thanks but it does not work.

However, your answer is a path to the solution.

I found this code but it does not work either.

I will study macros.

Regards.

Sub utf8() ' ' utf8 Makro ' Convert UTF-8 characters to Excel format ' ' key combination: Strg+w '

' a acute big
    Cells.Replace What:="Ã", Replacement:="Á", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a acute small
    Cells.Replace What:="Ã¡", Replacement:="á", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a brevis small
    Cells.Replace What:="Äƒ", Replacement:=ChrW(259), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a cedilla big
    Cells.Replace What:="Ä„", Replacement:=ChrW(260), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a cedilla small
    Cells.Replace What:="Ä…", Replacement:=ChrW(261), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a circumflex big
    Cells.Replace What:="Ã‚", Replacement:="Â", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a circumflex small
    Cells.Replace What:="Ã¢", Replacement:="â", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a grave small
    Cells.Replace What:="Ã ", Replacement:="à", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a macron big
    Cells.Replace What:="Ä€", Replacement:=ChrW(256), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a macron small
    Cells.Replace What:="Ä", Replacement:=ChrW(257), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a ring big
    Cells.Replace What:="Ã…", Replacement:="Å", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a ring small
    Cells.Replace What:="Ã¥", Replacement:="å", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a tilde small
    Cells.Replace What:="Ã£", Replacement:="ã", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a umlaut big
    Cells.Replace What:="Ã„", Replacement:="Ä", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' a umlaut small
    Cells.Replace What:="Ã¤", Replacement:="ä", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' ae ligature small
    Cells.Replace What:="Ã¦", Replacement:="æ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' ae ligature big
    Cells.Replace What:="Ã†", Replacement:="Æ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c acute big
    Cells.Replace What:="Ä†", Replacement:=ChrW(262), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c acute small
    Cells.Replace What:="Ä‡", Replacement:=ChrW(263), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c caron big
    Cells.Replace What:="ÄŒ", Replacement:=ChrW(268), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c caron small
    Cells.Replace What:="Ä", Replacement:=ChrW(269), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c cedilla big
    Cells.Replace What:="Ã‡", Replacement:="Ç", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' c cedilla small
    Cells.Replace What:="Ã§", Replacement:="ç", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' d dash big
    Cells.Replace What:="Ä", Replacement:=ChrW(272), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' d dash small
    Cells.Replace What:="Ä‘", Replacement:=ChrW(273), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' dh small small
    Cells.Replace What:="Ã°", Replacement:="ð", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e acute big
    Cells.Replace What:="Ã‰", Replacement:="É", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e acute small
    Cells.Replace What:="Ã©", Replacement:="é", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e caron small
    Cells.Replace What:="Ä›", Replacement:=ChrW(283), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e cedilla small
    Cells.Replace What:="Ä™", Replacement:=ChrW(281), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e circumflex small
    Cells.Replace What:="Ãª", Replacement:="ê", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e grave big
    Cells.Replace What:="Ãˆ", Replacement:="È", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e grave small
    Cells.Replace What:="Ã¨", Replacement:="è", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e macron big
    Cells.Replace What:="Ä’", Replacement:=ChrW(274), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e macron small
    Cells.Replace What:="Ä“", Replacement:=ChrW(275), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e point small
    Cells.Replace What:="Ä—", Replacement:=ChrW(279), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e turned big
    Cells.Replace What:="Æ", Replacement:=ChrW(399), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e turned small
    Cells.Replace What:="É™", Replacement:=ChrW(601), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
    Cells.Replace What:="Ç", Replacement:=ChrW(601), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' e umlaut small
    Cells.Replace What:="Ã«", Replacement:="ë", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' g brevis small
    Cells.Replace What:="ÄŸ", Replacement:=ChrW(287), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' g point big
    Cells.Replace What:="Ä ", Replacement:=ChrW(288), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' g point small
    Cells.Replace What:="Ä¡", Replacement:=ChrW(289), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' h macron big
    Cells.Replace What:="Ä¦", Replacement:=ChrW(294), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' h macron small
    Cells.Replace What:="Ä§", Replacement:=ChrW(295), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i acute big
    Cells.Replace What:="Ã", Replacement:="Í", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i acute small
    Cells.Replace What:="Ã", Replacement:="í", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i brevis small
    Cells.Replace What:="Ä", Replacement:=ChrW(301), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i circumflex big
    Cells.Replace What:="ÃŽ", Replacement:="Î", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i circumflex small
    Cells.Replace What:="Ã®", Replacement:="î", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i grave small
    Cells.Replace What:="Ã¬", Replacement:="ì", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i macron big
    Cells.Replace What:="Äª", Replacement:=ChrW(298), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i macron small
    Cells.Replace What:="Ä«", Replacement:=ChrW(299), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i pointed big
    Cells.Replace What:="Ä°", Replacement:=ChrW(304), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i pointless small
    Cells.Replace What:="Ä±", Replacement:=ChrW(305), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' i umlaut small
    Cells.Replace What:="Ã¯", Replacement:="ï", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' k cedilla big
    Cells.Replace What:="Ä¶", Replacement:=ChrW(310), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' k cedilla small
    Cells.Replace What:="Ä·", Replacement:=ChrW(311), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' l cedilla small
    Cells.Replace What:="Ä¼", Replacement:=ChrW(316), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' l slash big
    Cells.Replace What:="Å", Replacement:=ChrW(321), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' l slash small
    Cells.Replace What:="Å‚", Replacement:=ChrW(322), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n acute small
    Cells.Replace What:="Å„", Replacement:=ChrW(324), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n caron small
    Cells.Replace What:="Åˆ", Replacement:=ChrW(328), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n cedilla small
    Cells.Replace What:="Å†", Replacement:=ChrW(326), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n tilde big
    Cells.Replace What:="Ã‘", Replacement:="Ñ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' n tilde small
    Cells.Replace What:="Ã±", Replacement:="ñ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o acute big
    Cells.Replace What:="Ã“", Replacement:="Ó", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o acute small
    Cells.Replace What:="Ã³", Replacement:="ó", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o brevis big
    Cells.Replace What:="ÅŽ", Replacement:=ChrW(334), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o brevis small
    Cells.Replace What:="Å", Replacement:=ChrW(335), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o circumflex small
    Cells.Replace What:="Ã´", Replacement:="ô", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o double acute big
    Cells.Replace What:="Ã–", Replacement:=ChrW(336), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o double acute small
    Cells.Replace What:="Å‘", Replacement:=ChrW(337), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o grave small
    Cells.Replace What:="Ã²", Replacement:="ò", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o macron big
    Cells.Replace What:="ÅŒ", Replacement:=ChrW(332), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o macron small
    Cells.Replace What:="Å", Replacement:=ChrW(333), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o slash big
    Cells.Replace What:="Ã˜", Replacement:="Ø", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o slash small
    Cells.Replace What:="Ã¸", Replacement:="ø", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o tilde small
    Cells.Replace What:="Ãµ", Replacement:="õ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' o umlaut small
    Cells.Replace What:="Ã¶", Replacement:="ö", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' r caron big
    Cells.Replace What:="Å˜", Replacement:=ChrW(344), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' r caron small
    Cells.Replace What:="Å™", Replacement:=ChrW(345), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' r cedilla small
    Cells.Replace What:="Å—", Replacement:=ChrW(343), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s acute big
    Cells.Replace What:="Åš", Replacement:=ChrW(346), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s acute small
    Cells.Replace What:="Å›", Replacement:=ChrW(347), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s caron big
    Cells.Replace What:="Å ", Replacement:=ChrW(352), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s caron small
    Cells.Replace What:="Å¡", Replacement:=ChrW(353), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s cedilla big
    Cells.Replace What:="Åž", Replacement:=ChrW(350), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' s cedilla small
    Cells.Replace What:="ÅŸ", Replacement:=ChrW(351), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' sz ligature small
    Cells.Replace What:="ÃŸ", Replacement:="ß", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' t cedilla big
    Cells.Replace What:="Å¢", Replacement:=ChrW(354), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' t cedilla small
    Cells.Replace What:="Å£", Replacement:=ChrW(355), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' thorn big
    Cells.Replace What:="Ãž", Replacement:="Þ", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u acute big
    Cells.Replace What:="Ãš", Replacement:="Ú", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u acute small
    Cells.Replace What:="Ãº", Replacement:="ú", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u brevis big
    Cells.Replace What:="Å¬", Replacement:=ChrW(364), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u brevis small
    Cells.Replace What:="Å", Replacement:=ChrW(365), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u cedilla small
    Cells.Replace What:="Å³", Replacement:=ChrW(371), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u circumflex small
    Cells.Replace What:="Ã»", Replacement:="û", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u double acute small
    Cells.Replace What:="Å±", Replacement:=ChrW(369), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u grave small
    Cells.Replace What:="Ã¹", Replacement:="ù", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u macron big
    Cells.Replace What:="Åª", Replacement:=ChrW(362), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u macron small
    Cells.Replace What:="Å«", Replacement:=ChrW(363), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u ring small
    Cells.Replace What:="Å¯", Replacement:=ChrW(367), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u umlaut big
    Cells.Replace What:="Ãœ", Replacement:="Ü", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' u umlaut small
    Cells.Replace What:="Ã¼", Replacement:="ü", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' y acute small
    Cells.Replace What:="Ã½", Replacement:="ý", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z acute small
    Cells.Replace What:="Åº", Replacement:=ChrW(378), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z caron big
    Cells.Replace What:="Å½", Replacement:=ChrW(381), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z caron small
    Cells.Replace What:="Å¾", Replacement:=ChrW(382), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z point big
    Cells.Replace What:="Å»", Replacement:=ChrW(379), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' z point small
    Cells.Replace What:="Å¼", Replacement:=ChrW(380), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' cedilla
    Cells.Replace What:="Ì¨", Replacement:=ChrW(808), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' diaresis
    Cells.Replace What:="Ìˆ", Replacement:=ChrW(776), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' halfcircle, right open
    Cells.Replace What:="Â´", Replacement:=ChrW(703), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' macron
    Cells.Replace What:="Ì„", Replacement:=ChrW(772), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' macron below
    Cells.Replace What:="Ì±", Replacement:=ChrW(817), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True
' apostroph
    Cells.Replace What:="Ê¼", Replacement:=ChrW(700), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True

End Sub

Answer 2

Hi,

Try this:

Sub ReplaceSpecialCharacters()

Dim d

Set d = CreateObject("Scripting.Dictionary")

d.Add "Ã¨", "è"

d.Add "Ãˆ", "è"

d.Add "Ã¹", "ù"

d.Add "Ã ", "à"

d.Add "Ã¡", "á"

d.Add "Ã²", "ò"

d.Add "Ã-", "Ö"

d.Add "Ã–", "Ö"

d.Add "Ã„", "Ä"

For Each Key In d

Selection.Replace What:=Key, Replacement:=d(Key), LookAt:=xlPart, SearchOrder _

:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

Next Key

Set d = Nothing

End Sub

Answer 3

Hi,

try this...

step1

Save As, your Workbook with extension .xlsm (macros enabled)

step2

press ALT+F11 to open Visual Basic
Insert > module and paste the code below on the right

Sub ReplaceSymbols()Selection.Replace What:=ChrW(&HC3) & ChrW(&HA9), Replacement:=ChrW(&HE9), LookAt:=xlPartEnd Sub

Press ALT+Q to Close Visual Basic

step3

To run the macro, press ALT+F8,

select <ReplaceSymbols> from the list and click the run button.

Note

select the range and run the code.

Answer 4

Ashish Mathur 101.8K Volunteer Moderator

Hi,

I could find for Ã© and replace with é. I am using Excel 2013. Ensure that Find and Replace > Options, Match Entire cell contents is unchecked.

Hope this helps.

0 comments

Answer 5

Anonymous

It was weird.

It did not worked.

But when I am in XLSM (with macros activated), it works.

0 comments

Share via

find and replace all special characters (after an encoding problem)

7 answers