如何将CLEAN（）公式应用于整个Excel工作表？

I have an Excel file with almost 50 columns and 10,000 rows. These contain non-printable characters in some cells; I want to remove these characters from all cells in the sheet.

我有一个包含近50列和10,000行的Excel文件。这些在一些单元格中包含不可打印的字符;我想从工作表中的所有单元格中删除这些字符。

It would be difficult to apply the CLEAN() function to each and every column. Is there an alternative?

将CLEAN（）函数应用于每一列是很困难的。还有其他选择吗？

4 个解决方案

#1

I can't see it visually.. If you see that character at the end of the cell text, then you can find that value using the below code

我无法直观地看到它..如果你在单元格文本的末尾看到那个字符，那么你可以使用下面的代码找到该值

debug.Print asc(right(range("D1").Value,1))

Simply replace 110 with that value in the code below.

只需在下面的代码中用该值替换110即可。

Sub Sample()
    Dim ws As Worksheet

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ws.Cells.Replace What:=Chr(110), Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

HERE is the ASCII table for your reference.

这里是ASCII表供您参考。

#2

To apply the excel CLEAN function on each cell of the first 50 columns and 10000 rows:

要在前50列和10000行的每个单元格上应用excel CLEAN函数：

Sub cleanSheet()
    Dim r As Range
    Application.ScreenUpdating = False
    For Each r In Range(Cells(1, 1), Cells(10000, 50))
        r = WorksheetFunction.Clean(r)
    Next r
    Application.ScreenUpdating = True
End Sub

#3

You can simply create another sheet then, in the first cell, write the clean formula =Clean('sheet1'!A1) and enter.

您可以简单地创建另一个工作表，然后在第一个单元格中编写干净的公式= Clean（'sheet1'！A1）并输入。

Now drag the columns and rows. You will get your cleaned up data in this sheet. You can also use the trim function in the same way.

现在拖动列和行。您将在此表中获得已清理的数据。您也可以以相同的方式使用trim功能。

Hope this helps !

希望这可以帮助！

#4

Thank you z̫͋! This was just what I was looking for!

谢谢z̫͋！这正是我想要的！

I noticed in the comments there was some call to reference only non-empty cells. I used this instead of the cell references and it vastly improved the processing time.

我注意到在评论中有一些调用只引用非空单元格。我使用它而不是单元格引用，它大大改善了处理时间。

For Each r In ActiveSheet.UsedRange

Hopefully this helps a little. (Though I'm pretty late to the party.)

希望这有点帮助。（虽然我很晚才参加派对。）

#1