Excel宏将HTML实体转换为文本 [英] Excel macro to convert HTML entities to text

问题描述

我有一个巨大的Excel文件，其中包含在线调查的结果.进行调查的人在几个方面搞砸了格式，而我首先要照顾的搞砸就是将HTML实体转换为常规文本.

I have a huge Excel file that contains the result of an online survey. The person who built the survey messed up the formatting in several respects, and the mess-up I need to take care of first is converting HTML entities to regular text.

据我所见，仅使用了两个HTML实体,和"，但是文档超过12,000行，因此我不能确定没有使用其他HTML实体...以及是否使用了其他HTML实体曾经我想让它们也转换为文本.

From what I can see only two HTML entities are used, , and " but the document is over 12,000 rows so I cannot be sure there are no other HTML entities used... and if other HTML entities are used I want them converted to text as well.

我已经成功创建了一个宏，可以将我提到的两个HTML实体转换为文本，但是我不知道如何使宏在整个文件上执行(即，我必须按住宏热键才能执行它会执行...并且需要花费很多时间.)

I have successfully made a macro to convert the two HTML entities I mentioned into text, but I don't know how to make the macro execute on the entire file (i.e. I have to hold down on the macro hot key to make it execute... and it is taking forever).

如果已经有一个宏可以用来做我想做的事，那会很好，因为我还可以使用它的修改版本来完成下一个以适当顺序排列所有列和行的任务.

If there was a macro already available to do what I want that would be great because I could also use a modified version of it for my next task of arranging all the columns and rows in the proper order.

更新:

这是我搜索,的宏的版本.它可以正常工作，我只需要按住需要永久使用的热键即可.如果我可以在整个Excel文件上运行该文件，那将是一个不错的选择，然后我可以为每个HTML实体调整宏，直到将它们全部消除为止.

This is the version of my macro that searches for ,. It works, I just have to hold down on the hot key which takes forever. If I could make this run on the entire Excel file that would be great, and then I can just adjust the macro for each HTML entity until I have eliminated them all.

Sub Macro2()
'
' HTML_Converter Macro
'

'
    Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Replace What:=",", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
End Sub

推荐答案

创建工作簿的备份.

通过按 Alt + F11 打开VBA编辑器.

Open the VBA editor by pressing Alt+F11.

在您正在使用的工作簿下方的树视图中双击此工作簿".

Double-click "This Workbook" in the treeview at left under the workbook that you are working with.

复制并粘贴以下内容:

Sub UnescapeCharacters()

    ' set this to match your case
    sheetname = "Sheet1"

    Dim sheet As Worksheet
    Set sheet = Me.Worksheets(sheetname)

    For Row = 1 To sheet.UsedRange.Rows.Count
        For Column = 1 To sheet.UsedRange.Columns.Count
            Dim cell As Range
            Set cell = sheet.Cells(Row, Column)

            ' define all your replacements here
            ReplaceCharacter cell, """, """" 'quadruple quotes required
            ReplaceCharacter cell, ",", ","
        Next Column
    Next Row

End Sub

Sub ReplaceCharacter(ByRef cell As Range, ByVal find As String, ByVal replacement As String)

    Dim result As String
    cell.Value = replace(cell.Text, find, replacement, 1, -1)

End Sub

这只会遍历指定工作表中的每个单元格并替换您定义的所有内容.提供的代码替换了您提到的两个字符代码.

This just iterates over every cell in the specified worksheet and replaces everything you define. The provided code replaces the two character codes you mentioned.

您可以将其作为宏运行，也可以将插入符号放在"UnescapeCharacters"子例程中，然后按 F5 .

You can run it as a macro, or just place the caret in the "UnescapeCharacters" subroutine and hit F5.

这篇关于Excel宏将HTML实体转换为文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！