|
Speical Characters
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 |
Speical Characters
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" wrote in message ... 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 |
Special Characters
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" wrote in message ... 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 |
Speical Characters
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: http://www.excelforum.com/member.php...o&userid=32413 View this thread: http://www.excelforum.com/showthread...hreadid=521925 |
| All times are GMT +1. The time now is 04:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com