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.

--
Regards,
Fred


"Tallguy63" <[email protected]> wrote in
message news:[email protected]...
>
> 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?
>
>
> --
> Tallguy63
> ------------------------------------------------------------------------
> Tallguy63's Profile:
> http://www.excelforum.com/member.php...o&userid=32413
> View this thread: http://www.excelforum.com/showthread...hreadid=521925
>

[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



---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Tallguy63" <[email protected]> wrote in message
news:[email protected]...
>
> 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?
>
>
> --
> Tallguy63
> ------------------------------------------------------------------------
> Tallguy63's Profile: http://www.excelforum.com/member.php...o&userid=32413
> View this thread: http://www.excelforum.com/showthread...hreadid=521925
>

[Tallguy63]

Thanks David,

It looks like the macro should do the job. I have it saved in my Word Program. How do I import the macro into my excel work sheet and run the macro? As you can see I am really a novice to macros.