| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to replace invalid filename characters | Excel Worksheet Functions | |||
| Remove varying amounts of space characters | Excel Discussion (Misc queries) | |||
| Help - Can't directly input Japanese characters into cell | Excel Discussion (Misc queries) | |||
| HOW DO I EXTRACT ALL CHARACTERS AFTER 5 CHARACTERS ON LEFT | Excel Worksheet Functions | |||
| Max number of characters in a cell | Excel Discussion (Misc queries) | |||
Linear Mode
