跳到主要内容

如何在Excel中将重音字符替换为常规字符?

您是否在Microsoft Excel中收到一些带有重音符号的报告? 遍历整个工作簿并用人工将带重音符号的字符替换为常规字符似乎很乏味,例如“û到“ u”。本文将介绍一些棘手的内容,以便在Excel中用常规字符快速替换重音字符。


用VBA将重音字符替换为常规字符

对于熟悉Microsoft Excel的用户,使用VBA宏是处理此复杂工作的简便方法。

1. 按住 其他 + F11 键,然后打开 Microsoft Visual Basic for Applications窗口.

2. 点击 插页 > 模块,然后将以下宏粘贴到 模块窗口.

Function StripAccent(thestring As String)
Dim A As String * 1
Dim B As String * 1
Dim i As Integer
Const AccChars= "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const RegChars= "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
thestring = Replace(thestring, A, B)
Next
StripAccent = thestring
End Function

3. 然后转到空白单元格并将公式粘贴到单元格中: = CheckStringCHAR(InString), 例如 = CheckStringCHAR(“ù”)= CheckStringCHAR(A2).

笔记:
(1)如果Microsoft Visual Basic不支持Unicode,则可能根本无法识别带重音的字符。 例如,在Visual Basic中粘贴“š”时,可能会得到“?”。 如果是这样,则此宏不可用。
(2)它不会直接替换所选单元格字符串中的重音字符。


使用 Kutools for Excel 将重音字符替换为常规字符

使用VBA宏需要太多的技能和知识。 而且以下方法将使您仅需单击几下即可更轻松地工作。 Kutools for Excel's 替换重音字符工具使您可以轻松地立即用常规字符替换各种重音字符。

Kutools for Excel - 使用 300 多种基本工具增强 Excel 功能。 享受全功能 30 天免费试用,无需信用卡! 立即行动吧!

1. 选择要替换所有重音字符的范围。

2. 点击 库工具 > 文本 > 替换重音字符…,请参见屏幕截图:

3. In 替换重音字符 对话框中,单击 全选 按钮选择所有替换规则,然后单击确定按钮替换所有重音字符。 看截图:

:
(1)如果没有想要的特定重音字符,则可以单击 添加规则 按钮,将您自己的规则添加到“替换重音字符”对话框上方的列表框中。
(2)您可以在“替换重音字符”对话框中自定义新的替换规则。

现在,您将立即将选定范围内的所有重音字符转换为常规字符。 看截图:

替换重音字符 Kutools for Excel 的功能可以轻松地将整个工作簿中的各种重音字符替换为常规字符。 免费试用!

最佳办公生产力工具

热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

kte选项卡201905


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How can I replace multiple characters with a single one? For example é with e?
This comment was minimized by the moderator on the site
Dobrý deň, starší súbor xlsm mi vo win11 debuguje na tom, že nevie nájsť meno hárka "Prehľad", má problém s diakritikou.
Nie je problém zmeniť pomenovanie hárka na nové bez diakritiky resp. vyhľadať objekt inak, oveľa väčší problém je však zároveň ten,
že z tohto súboru v exceli pomocou VBA odosielam emaily a ak chcem pomocou VBA napísať telo emailu, tak sa mi v outlooku
menia písmená s diakritikou (č, ď, ť ...) na rôzne zástupné znaky. Email vyzerá nedôstojne a nechcem ho písať ani bez diakritiky,
pretože sme predsa na Slovensku a komunikujeme ešte v materinskej reči...
Môžete mi prosím napísať, či sa tento problém dá odstrániť konkrétnym nastavením windows alebo officu?
V predošlom počítači to fungovalo bez problémov.
Ďakujem pekne.
This comment was minimized by the moderator on the site
I tried that VBA script on my worksheet which had several names with accent characters. This did a nice job of cleaning them up. Thanks.
This comment was minimized by the moderator on the site
I have added the code and it work perfectly however I need to add more accent to the code as I´m missing polish accents. How can I edit the code and add those accents? I try to do it in a notepad and then paste again the code but it change the letter back to normal characters. I'm not an expert so I really need your help please!
This comment was minimized by the moderator on the site
Const AccChars = "ŠŽšžYAÁÂAÄAÇEÉEËIÍÎI?NOóÔOÖUÚUÜÝaáâaäaçeéeëiíîi?noóôoöuúuüýyĄĘĆŁŃóŚŻŹąęćłńóśżź"
Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyyAECLNOSZZaeclnoszz"
This comment was minimized by the moderator on the site
The VBA function StripAccent() worked like a charm, thank you very much for it. But I would like to ask what exactly does this "* 1" at at the end of "Dim A As String * 1" initialization string, I've never seen that before and I wasn't able to find an answer to that myself so far.
This comment was minimized by the moderator on the site
when you remove special character it also remove the space between the text ??
This comment was minimized by the moderator on the site
is it possible to replace multiple characters with another character? There is no way to say I want to replace é by é

How can I do mass replacements like this in Kutools for Excel? I like the multireplace functionality but it would be a lot more useful if replacement of entire words would be possible. Anyboy any idea how I can do this?
This comment was minimized by the moderator on the site
If you are looking to replace a specific accent character (ex. é), you can do a Find and Replace All (search for "é" and replace with "e"). This solved my problem.
This comment was minimized by the moderator on the site
No shit Sherlock
This comment was minimized by the moderator on the site
Hello, Excellent Macro. Thanks for posting this. However I am wondering if I want to replace all of the characters in the excel file, how can I do that at once? Thanks Vivek
This comment was minimized by the moderator on the site
On the "Replace accented characters with regular characters with VBA" part, step 3, you must replace CheckStringCHAR for STRIPACCENT, which is the name of the function in the provided code!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations