Speical Characters

[Tallguy63]

I deal with worksheets that have numerous foreign letters
(¡ Ñ Ú ª È Ò À Ì) and special characters. Is there a way
to find and replace them without using replace option one letter at a
time?

 

[Fred Smith]

A macro is the only way I can think of to do this. If you're interested in this
solution, post the query to the .programming group. Someone is sure to help you
out.

 

[David McRitchie]

Hi ...,

Here is a macro to change within a selection (limited to usedrange)
you will have to verify yourself that it is what you want.

Convert ( translate / replace) non English language letters with English typewriter (typwritten) letters.

If you find mistakes, please correct them in the newsgroup, because someone else will
probably want to do the same.

If viewing from Google you would probably have to look at the original version of the post,
as HTML will possibly mess this up, as might your newsreader.




Sub Macro2()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Intersect(Selection, ActiveSheet.UsedRange)
.Replace what:=Chr(138), replacement:="S", lookat:=xlPart, MatchCase:=True '-- Š -- S
.Replace what:=Chr(140), replacement:="OE", lookat:=xlPart, MatchCase:=True '-- Π-- OE
.Replace what:=Chr(142), replacement:="Z", lookat:=xlPart, MatchCase:=True '-- Ž -- Z
.Replace what:=Chr(156), replacement:="oe", lookat:=xlPart, MatchCase:=True '-- œ -- oe
.Replace what:=Chr(158), replacement:="z", lookat:=xlPart, MatchCase:=True '-- ž -- z
.Replace what:=Chr(159), replacement:="Y", lookat:=xlPart, MatchCase:=True '-- Ÿ -- Y
.Replace what:=Chr(161), replacement:="i", lookat:=xlPart, MatchCase:=True '-- ¡ -- i
.Replace what:=Chr(170), replacement:="a", lookat:=xlPart, MatchCase:=True '-- ª -- a
.Replace what:=Chr(191), replacement:="?", lookat:=xlPart, MatchCase:=True '-- ¿ -- ?
.Replace what:=Chr(192), replacement:="A", lookat:=xlPart, MatchCase:=True '-- À -- A
.Replace what:=Chr(193), replacement:="A", lookat:=xlPart, MatchCase:=True '-- Á -- A
.Replace what:=Chr(194), replacement:="A", lookat:=xlPart, MatchCase:=True '-- Â -- A
.Replace what:=Chr(195), replacement:="A", lookat:=xlPart, MatchCase:=True '-- Ã -- A
.Replace what:=Chr(196), replacement:="A", lookat:=xlPart, MatchCase:=True '-- Ä -- A
.Replace what:=Chr(197), replacement:="A", lookat:=xlPart, MatchCase:=True '-- Å -- A
.Replace what:=Chr(198), replacement:="AE", lookat:=xlPart, MatchCase:=True '-- Æ -- AE
.Replace what:=Chr(199), replacement:="C", lookat:=xlPart, MatchCase:=True '-- Ç -- C
.Replace what:=Chr(200), replacement:="E", lookat:=xlPart, MatchCase:=True '-- È -- E
.Replace what:=Chr(201), replacement:="E", lookat:=xlPart, MatchCase:=True '-- É -- E
.Replace what:=Chr(202), replacement:="E", lookat:=xlPart, MatchCase:=True '-- Ê -- E
.Replace what:=Chr(203), replacement:="E", lookat:=xlPart, MatchCase:=True '-- Ë -- E
.Replace what:=Chr(204), replacement:="I", lookat:=xlPart, MatchCase:=True '-- Ì -- I
.Replace what:=Chr(205), replacement:="I", lookat:=xlPart, MatchCase:=True '-- Í -- I
.Replace what:=Chr(206), replacement:="I", lookat:=xlPart, MatchCase:=True '-- Î -- I
.Replace what:=Chr(207), replacement:="I", lookat:=xlPart, MatchCase:=True '-- Ï -- I
.Replace what:=Chr(208), replacement:="D", lookat:=xlPart, MatchCase:=True '-- Ð -- D
.Replace what:=Chr(209), replacement:="N", lookat:=xlPart, MatchCase:=True '-- Ñ -- N
.Replace what:=Chr(210), replacement:="O", lookat:=xlPart, MatchCase:=True '-- Ò -- O
.Replace what:=Chr(211), replacement:="O", lookat:=xlPart, MatchCase:=True '-- Ó -- O
.Replace what:=Chr(212), replacement:="O", lookat:=xlPart, MatchCase:=True '-- Ô -- O
.Replace what:=Chr(213), replacement:="O", lookat:=xlPart, MatchCase:=True '-- Õ -- O
.Replace what:=Chr(214), replacement:="O", lookat:=xlPart, MatchCase:=True '-- Ö -- O
.Replace what:=Chr(215), replacement:="x", lookat:=xlPart, MatchCase:=True '-- × -- x
.Replace what:=Chr(216), replacement:="O", lookat:=xlPart, MatchCase:=True '-- Ø -- O
.Replace what:=Chr(217), replacement:="U", lookat:=xlPart, MatchCase:=True '-- Ù -- U
.Replace what:=Chr(218), replacement:="U", lookat:=xlPart, MatchCase:=True '-- Ú -- U
.Replace what:=Chr(219), replacement:="U", lookat:=xlPart, MatchCase:=True '-- Û -- U
.Replace what:=Chr(220), replacement:="U", lookat:=xlPart, MatchCase:=True '-- Ü -- U
.Replace what:=Chr(221), replacement:="Y", lookat:=xlPart, MatchCase:=True '-- Ý -- Y
.Replace what:=Chr(222), replacement:="Þ", lookat:=xlPart, MatchCase:=True '-- Þ -- Þ
.Replace what:=Chr(223), replacement:="ss", lookat:=xlPart, MatchCase:=True '-- ß -- ss
.Replace what:=Chr(224), replacement:="a", lookat:=xlPart, MatchCase:=True '-- à -- a
.Replace what:=Chr(225), replacement:="a", lookat:=xlPart, MatchCase:=True '-- á -- a
.Replace what:=Chr(226), replacement:="a", lookat:=xlPart, MatchCase:=True '-- â -- a
.Replace what:=Chr(227), replacement:="a", lookat:=xlPart, MatchCase:=True '-- ã -- a
.Replace what:=Chr(228), replacement:="a", lookat:=xlPart, MatchCase:=True '-- ä -- a
.Replace what:=Chr(229), replacement:="a", lookat:=xlPart, MatchCase:=True '-- å -- a
.Replace what:=Chr(230), replacement:="ae", lookat:=xlPart, MatchCase:=True '-- æ -- ae
.Replace what:=Chr(231), replacement:="c", lookat:=xlPart, MatchCase:=True '-- ç -- c
.Replace what:=Chr(232), replacement:="e", lookat:=xlPart, MatchCase:=True '-- è -- e
.Replace what:=Chr(233), replacement:="e", lookat:=xlPart, MatchCase:=True '-- é -- e
.Replace what:=Chr(234), replacement:="e", lookat:=xlPart, MatchCase:=True '-- ê -- e
.Replace what:=Chr(235), replacement:="e", lookat:=xlPart, MatchCase:=True '-- ë -- e
.Replace what:=Chr(236), replacement:="i", lookat:=xlPart, MatchCase:=True '-- ì -- i
.Replace what:=Chr(237), replacement:="i", lookat:=xlPart, MatchCase:=True '-- í -- i
.Replace what:=Chr(238), replacement:="j", lookat:=xlPart, MatchCase:=True '-- î -- j
.Replace what:=Chr(239), replacement:="k", lookat:=xlPart, MatchCase:=True '-- ï -- k
.Replace what:=Chr(240), replacement:="o", lookat:=xlPart, MatchCase:=True '-- ð -- o
.Replace what:=Chr(241), replacement:="n", lookat:=xlPart, MatchCase:=True '-- ñ -- n
.Replace what:=Chr(242), replacement:="o", lookat:=xlPart, MatchCase:=True '-- ò -- o
.Replace what:=Chr(243), replacement:="o", lookat:=xlPart, MatchCase:=True '-- ó -- o
.Replace what:=Chr(244), replacement:="o", lookat:=xlPart, MatchCase:=True '-- ô -- o
.Replace what:=Chr(245), replacement:="o", lookat:=xlPart, MatchCase:=True '-- õ -- o
.Replace what:=Chr(246), replacement:="o", lookat:=xlPart, MatchCase:=True '-- ö -- o
.Replace what:=Chr(248), replacement:="o", lookat:=xlPart, MatchCase:=True '-- ø -- o
.Replace what:=Chr(249), replacement:="u", lookat:=xlPart, MatchCase:=True '-- ù -- u
.Replace what:=Chr(250), replacement:="u", lookat:=xlPart, MatchCase:=True '-- ú -- u
.Replace what:=Chr(251), replacement:="u", lookat:=xlPart, MatchCase:=True '-- û -- u
.Replace what:=Chr(252), replacement:="u", lookat:=xlPart, MatchCase:=True '-- ü -- u
.Replace what:=Chr(253), replacement:="y", lookat:=xlPart, MatchCase:=True '-- ý -- y
.Replace what:=Chr(254), replacement:="þ", lookat:=xlPart, MatchCase:=True '-- þ -- þ
.Replace what:=Chr(255), replacement:="y", lookat:=xlPart, MatchCase:=True '-- ÿ -- y
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Tallguy63]

I have a macro to change the special characters into English language
characters saved in my Word Program. How do I import the macro into my
excel work sheet and run the macro?