如何在整个Excel工作表中应用CLEAN()公式？

Question

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

对每个列应用函数会很困难。是否有其他方法？

- Madhu Sudhan Reddy

---

你知道那些“不可打印字符”是什么吗？ - Siddharth Rout

我的意思是，你知道这些字符的ASC码吗？ - Siddharth Rout

嗨，Siddharth，我不确定字符t。它们不是ASCII字符。即使我无法看到它们，它们也会像方框和问号一样可见。 - Madhu Sudhan Reddy

您可以从此链接下载Excel文件，并观察D1和E1单元格。 - Madhu Sudhan Reddy

FYI，来自CLEAN文档：在Unicode字符集中（Unicode：由Unicode联盟开发的字符编码标准。通过使用多个字节来表示每个字符，Unicode使世界上几乎所有书面语言都可以使用单个字符集来表示），存在其他非打印字符（值为127、129、141、143、144和157）。仅靠CLEAN函数无法删除这些额外的非打印字符。 - Jean-François Corbett

Answer 1

我无法从视觉上看到它。如果您在单元格文本的末尾看到该字符，则可以使用以下代码找到该值：

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

只需在下面的代码中将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

以下是您参考的ASCII表格：

这里

。关于IT技术方面的内容，需要您提供更具体的信息。

- Siddharth Rout

Answer 2

你可以简单地创建另一个工作表，然后在第一个单元格中编写清洁的公式=Clean('sheet1'!A1)，并输入。现在拖动列和行，你将在这个工作表中得到清理过的数据。你也可以以相同的方式使用修剪函数。希望这可以帮助！

- Isha

Answer 3

要在前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

- z̫͋

---

仅供参考，我在我的机器上运行了这个程序，看看需要多长时间；大约需要1小时。循环遍历单元格可能会非常缓慢。 - Jean-François Corbett

@Jean-FrançoisCorbett 在我的机器上不到一分钟就可以完成。 - z̫͋

@Jean-FrançoisCorbett 如果您知道一种限制循环为非空单元格的方法，我非常感兴趣。 - z̫͋

Answer 4

谢谢 z̫͋！这正是我在寻找的东西！

我注意到评论中有一些要求只引用非空单元格。我使用了这个方法而不是单元格引用，它极大地提高了处理时间。

For Each r In ActiveSheet.UsedRange

希望这能有所帮助。（虽然我来晚了一些。）

- Rosalinda Albrecht