software

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: www.excelforum.com/member.php...oamp;userid=32413
View this thread: www.excelforum.com/showthread...hreadid=521925A 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,
Fredquot;Tallguy63quot; gt; wrote in
message ...
gt;
gt; I deal with worksheets that have numerous foreign letters
gt; (¡ Ñ Ú ª È Ò À Ì) and special characters. Is there a way
gt; to find and replace them without using replace option one letter at a
gt; time?
gt;
gt;
gt; --
gt; Tallguy63
gt; ------------------------------------------------------------------------
gt; Tallguy63's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32413
gt; View this thread: www.excelforum.com/showthread...hreadid=521925
gt;
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:=quot;Squot;, lookat:=xlPart, MatchCase:=True '-- Š -- S
.Replace what:=Chr(140), replacement:=quot;OEquot;, lookat:=xlPart, MatchCase:=True '-- Π-- OE
.Replace what:=Chr(142), replacement:=quot;Zquot;, lookat:=xlPart, MatchCase:=True '-- Ž -- Z
.Replace what:=Chr(156), replacement:=quot;oequot;, lookat:=xlPart, MatchCase:=True '-- œ -- oe
.Replace what:=Chr(158), replacement:=quot;zquot;, lookat:=xlPart, MatchCase:=True '-- ž -- z
.Replace what:=Chr(159), replacement:=quot;Yquot;, lookat:=xlPart, MatchCase:=True '-- Ÿ -- Y
.Replace what:=Chr(161), replacement:=quot;iquot;, lookat:=xlPart, MatchCase:=True '-- ¡ -- i
.Replace what:=Chr(170), replacement:=quot;aquot;, lookat:=xlPart, MatchCase:=True '-- ª -- a
.Replace what:=Chr(191), replacement:=quot;?quot;, lookat:=xlPart, MatchCase:=True '-- ¿ -- ?
.Replace what:=Chr(192), replacement:=quot;Aquot;, lookat:=xlPart, MatchCase:=True '-- À -- A
.Replace what:=Chr(193), replacement:=quot;Aquot;, lookat:=xlPart, MatchCase:=True '-- Á -- A
.Replace what:=Chr(194), replacement:=quot;Aquot;, lookat:=xlPart, MatchCase:=True '-- Â -- A
.Replace what:=Chr(195), replacement:=quot;Aquot;, lookat:=xlPart, MatchCase:=True '-- Ã -- A
.Replace what:=Chr(196), replacement:=quot;Aquot;, lookat:=xlPart, MatchCase:=True '-- Ä -- A
.Replace what:=Chr(197), replacement:=quot;Aquot;, lookat:=xlPart, MatchCase:=True '-- Å -- A
.Replace what:=Chr(198), replacement:=quot;AEquot;, lookat:=xlPart, MatchCase:=True '-- Æ -- AE
.Replace what:=Chr(199), replacement:=quot;Cquot;, lookat:=xlPart, MatchCase:=True '-- Ç -- C
.Replace what:=Chr(200), replacement:=quot;Equot;, lookat:=xlPart, MatchCase:=True '-- È -- E
.Replace what:=Chr(201), replacement:=quot;Equot;, lookat:=xlPart, MatchCase:=True '-- É -- E
.Replace what:=Chr(202), replacement:=quot;Equot;, lookat:=xlPart, MatchCase:=True '-- Ê -- E
.Replace what:=Chr(203), replacement:=quot;Equot;, lookat:=xlPart, MatchCase:=True '-- Ë -- E
.Replace what:=Chr(204), replacement:=quot;Iquot;, lookat:=xlPart, MatchCase:=True '-- Ì -- I
.Replace what:=Chr(205), replacement:=quot;Iquot;, lookat:=xlPart, MatchCase:=True '-- Í -- I
.Replace what:=Chr(206), replacement:=quot;Iquot;, lookat:=xlPart, MatchCase:=True '-- Î -- I
.Replace what:=Chr(207), replacement:=quot;Iquot;, lookat:=xlPart, MatchCase:=True '-- Ï -- I
.Replace what:=Chr(208), replacement:=quot;Dquot;, lookat:=xlPart, MatchCase:=True '-- Ð -- D
.Replace what:=Chr(209), replacement:=quot;Nquot;, lookat:=xlPart, MatchCase:=True '-- Ñ -- N
.Replace what:=Chr(210), replacement:=quot;Oquot;, lookat:=xlPart, MatchCase:=True '-- Ò -- O
.Replace what:=Chr(211), replacement:=quot;Oquot;, lookat:=xlPart, MatchCase:=True '-- Ó -- O
.Replace what:=Chr(212), replacement:=quot;Oquot;, lookat:=xlPart, MatchCase:=True '-- Ô -- O
.Replace what:=Chr(213), replacement:=quot;Oquot;, lookat:=xlPart, MatchCase:=True '-- Õ -- O
.Replace what:=Chr(214), replacement:=quot;Oquot;, lookat:=xlPart, MatchCase:=True '-- Ö -- O
.Replace what:=Chr(215), replacement:=quot;xquot;, lookat:=xlPart, MatchCase:=True '-- × -- x
.Replace what:=Chr(216), replacement:=quot;Oquot;, lookat:=xlPart, MatchCase:=True '-- Ø -- O
.Replace what:=Chr(217), replacement:=quot;Uquot;, lookat:=xlPart, MatchCase:=True '-- Ù -- U
.Replace what:=Chr(218), replacement:=quot;Uquot;, lookat:=xlPart, MatchCase:=True '-- Ú -- U
.Replace what:=Chr(219), replacement:=quot;Uquot;, lookat:=xlPart, MatchCase:=True '-- Û -- U
.Replace what:=Chr(220), replacement:=quot;Uquot;, lookat:=xlPart, MatchCase:=True '-- Ü -- U
.Replace what:=Chr(221), replacement:=quot;Yquot;, lookat:=xlPart, MatchCase:=True '-- Ý -- Y
.Replace what:=Chr(222), replacement:=quot;Þquot;, lookat:=xlPart, MatchCase:=True '-- Þ -- Þ
.Replace what:=Chr(223), replacement:=quot;ssquot;, lookat:=xlPart, MatchCase:=True '-- ß -- ss
.Replace what:=Chr(224), replacement:=quot;aquot;, lookat:=xlPart, MatchCase:=True '-- à -- a
.Replace what:=Chr(225), replacement:=quot;aquot;, lookat:=xlPart, MatchCase:=True '-- á -- a
.Replace what:=Chr(226), replacement:=quot;aquot;, lookat:=xlPart, MatchCase:=True '-- â -- a
.Replace what:=Chr(227), replacement:=quot;aquot;, lookat:=xlPart, MatchCase:=True '-- ã -- a
.Replace what:=Chr(228), replacement:=quot;aquot;, lookat:=xlPart, MatchCase:=True '-- ä -- a
.Replace what:=Chr(229), replacement:=quot;aquot;, lookat:=xlPart, MatchCase:=True '-- å -- a
.Replace what:=Chr(230), replacement:=quot;aequot;, lookat:=xlPart, MatchCase:=True '-- æ -- ae
.Replace what:=Chr(231), replacement:=quot;cquot;, lookat:=xlPart, MatchCase:=True '-- ç -- c
.Replace what:=Chr(232), replacement:=quot;equot;, lookat:=xlPart, MatchCase:=True '-- è -- e
.Replace what:=Chr(233), replacement:=quot;equot;, lookat:=xlPart, MatchCase:=True '-- é -- e
.Replace what:=Chr(234), replacement:=quot;equot;, lookat:=xlPart, MatchCase:=True '-- ê -- e
.Replace what:=Chr(235), replacement:=quot;equot;, lookat:=xlPart, MatchCase:=True '-- ë -- e
.Replace what:=Chr(236), replacement:=quot;iquot;, lookat:=xlPart, MatchCase:=True '-- ì -- i
.Replace what:=Chr(237), replacement:=quot;iquot;, lookat:=xlPart, MatchCase:=True '-- í -- i
.Replace what:=Chr(238), replacement:=quot;jquot;, lookat:=xlPart, MatchCase:=True '-- î -- j
.Replace what:=Chr(239), replacement:=quot;kquot;, lookat:=xlPart, MatchCase:=True '-- ï -- k
.Replace what:=Chr(240), replacement:=quot;oquot;, lookat:=xlPart, MatchCase:=True '-- ð -- o
.Replace what:=Chr(241), replacement:=quot;nquot;, lookat:=xlPart, MatchCase:=True '-- ñ -- n
.Replace what:=Chr(242), replacement:=quot;oquot;, lookat:=xlPart, MatchCase:=True '-- ò -- o
.Replace what:=Chr(243), replacement:=quot;oquot;, lookat:=xlPart, MatchCase:=True '-- ó -- o
.Replace what:=Chr(244), replacement:=quot;oquot;, lookat:=xlPart, MatchCase:=True '-- ô -- o
.Replace what:=Chr(245), replacement:=quot;oquot;, lookat:=xlPart, MatchCase:=True '-- õ -- o
.Replace what:=Chr(246), replacement:=quot;oquot;, lookat:=xlPart, MatchCase:=True '-- ö -- o
.Replace what:=Chr(248), replacement:=quot;oquot;, lookat:=xlPart, MatchCase:=True '-- ø -- o
.Replace what:=Chr(249), replacement:=quot;uquot;, lookat:=xlPart, MatchCase:=True '-- ù -- u
.Replace what:=Chr(250), replacement:=quot;uquot;, lookat:=xlPart, MatchCase:=True '-- ú -- u
.Replace what:=Chr(251), replacement:=quot;uquot;, lookat:=xlPart, MatchCase:=True '-- û -- u
.Replace what:=Chr(252), replacement:=quot;uquot;, lookat:=xlPart, MatchCase:=True '-- ü -- u
.Replace what:=Chr(253), replacement:=quot;yquot;, lookat:=xlPart, MatchCase:=True '-- ý -- y
.Replace what:=Chr(254), replacement:=quot;þquot;, lookat:=xlPart, MatchCase:=True '-- þ -- þ
.Replace what:=Chr(255), replacement:=quot;yquot;, 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: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;Tallguy63quot; gt; wrote in message
...
gt;
gt; I deal with worksheets that have numerous foreign letters
gt; (¡ Ñ Ú ª È Ò À Ì) and special characters. Is there a way
gt; to find and replace them without using replace option one letter at a
gt; time?
gt;
gt;
gt; --
gt; Tallguy63
gt; ------------------------------------------------------------------------
gt; Tallguy63's Profile: www.excelforum.com/member.php...oamp;userid=32413
gt; View this thread: www.excelforum.com/showthread...hreadid=521925
gt;

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?--
Tallguy63
------------------------------------------------------------------------
Tallguy63's Profile: www.excelforum.com/member.php...oamp;userid=32413
View this thread: www.excelforum.com/showthread...hreadid=521925