excel - 改进了清除非打印字符和其他 ASCII 字符的功能

Question

我有一个 Excel 2013 工作簿，其中包含从各种来源导入的工作表。

它们在 clean 函数不考虑的地方都包含 Unicode 字符。

我发现了一个可以逐个单元格工作的函数，但我希望将其用于一系列单元格，而不必将该函数单独放入每个单元格中。

有人可以帮我转换这个函数吗？

谢谢

  Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)

  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)

'Call function == use CleanTrim just like it was a built-in Excel function. For example, =CleanTrim(B2)
End Function

Answer 1

这是我编写的子程序，经测试有效。

Sub CleanCells()
  Dim x As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)

  Dim rng As Range
    Dim row As Range
    Dim cell As Range

    Set rng = Range("A1:A168")

    For Each row In rng.Rows
        For Each cell In row.Cells
        'Do Something
                S = Replace(S, Chr(160), " ")
                For x = LBound(CodesToClean) To UBound(CodesToClean)
                  If InStr(S, Chr(CodesToClean(x))) Then S = Replace(S, Chr(CodesToClean(x)), "")
                Next
                WorksheetFunction.Trim (S)

        Next cell
    Next row

End Sub