Excel 宏将 HTML 实体转换为文本

发布于 2024-10-11 00:05:51 字数 1202 浏览 6 评论 0原文

我有一个巨大的 Excel 文件，其中包含在线调查的结果。构建调查的人在几个方面搞乱了格式，而我首先需要解决的混乱是将 HTML 实体转换为常规文本。

据我所知，只使用了两个 HTML 实体， , 和 " 但该文档超过 12,000 行，因此我无法确定是否有没有使用其他 HTML 实体...如果使用其他 HTML 实体，我希望它们也转换为文本。

我已经成功制作了一个宏来将我提到的两个 HTML 实体转换为文本，但我不知道如何使宏在整个文件上执行（即我必须按住宏热键才能使其执行）。 ..而且这需要永远）。

如果已经有一个宏可以执行我想要的操作，那就太好了，因为我还可以使用它的修改版本来完成下一个任务，即按正确的顺序排列所有列和行。

更新：

这是我的宏搜索,的版本。它有效，我只需要按住热键就可以了。如果我可以在整个 Excel 文件上运行此操作，那就太好了，然后我可以调整每个 HTML 实体的宏，直到将它们全部消除。

Sub Macro2()
'
' HTML_Converter Macro
'

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

原文

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.

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.

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.

UPDATE:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

想挽留 2024-10-18 00:05:51

创建工作簿的备份。

按 Alt+F11 打开 VBA 编辑器。

在左侧树视图中您正在使用的工作簿下方双击“此工作簿”。

复制并粘贴以下内容：

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

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

您可以将其作为宏运行，或者只需将插入符号放在“UnescapeCharacters”子例程中并按 F5。

Create a backup of the workbook.

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.

Copy and paste the following:

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.

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

回复收藏 0 原文