Excel VBA：从数字字段WITHOUT循环中删除非数字字符 [英] Excel VBA: Remove non-numeric characters from numeric fields WITHOUT loop

问题描述

我已经阅读了一些解释其中一个问题的主题，这将是有效的，但是会非常缓慢。解释如下：

I have read some topics explaining one of doing this, which would work, but would be incredibly slow. The explanation is here:

https://www.extendoffice.com/documents/excel/651-excel-remove-non-numeric-characters.html

它涉及遍历一个范围内的每个单元格，然后迭代字段中的字符，如果它们不匹配 [0-9] 则删除它们。我想看看有没有人有其他更有效的建议。

It involves iterating through each cell in a range and then iterating through the characters in the field and removing them if they do not match [0-9]. I wanted to see if anyone had any other more efficient suggestions.

一个想到的是将单元格内容加载到一个数组中，遍历它，并分割每个条目进入自己的数组来迭代。

One that comes to mind is loading the cell contents into an array, iterating through it, and splitting each entry into its own array to iterate through.

推荐答案

对于VBA方面的事情（注意循环），我决定满足我的自己对好几种不同方法的表现。所有这些都将范围拉到一个阵列中并在其上工作。链接的文章将被这些的任何的速度杀死，只是由于读取和写入单个单元格值的开销。

For the VBA side of things (note the loops), I decided to satisfy my own curiosity about the performance of a couple different methods. All of them pull the range into an array and work on it in place. The linked article will get killed in speed by any of these, simply due to the overhead in reading and writing single cell values.

对于第一种方法，我优化了链接文章中的代码有点：

For the first method, I optimized the code from the linked article "a bit":

Private Sub MidMethod(values() As Variant)
    Dim r As Long, c As Long, i As Long
    Dim temp As String, output As String

    For r = LBound(values, 1) To UBound(values, 1)
         For c = LBound(values, 2) To UBound(values, 2)
            output = vbNullString
            For i = 1 To Len(values(r, c))
                temp = Mid$(values(r, c), i, 1)
                If temp Like "[0-9]" Then
                    output = output & temp
                End If
            Next
            values(r, c) = output
         Next
    Next
End Sub

对于第二种方法，我使用 RegExp.Replace ：

For the second method I used RegExp.Replace:

Private Sub RegexMethod(values() As Variant)
    Dim r As Long, c As Long, i As Long

    With New RegExp
        .Pattern = "[^0-9]"
        .MultiLine = True
        .Global = True
        For r = LBound(values, 1) To UBound(values, 1)
             For c = LBound(values, 2) To UBound(values, 2)
                values(r, c) = .Replace(values(r, c), vbNullString)
             Next
        Next
    End With
End Sub

最后，对于最后一个方法，我使用了一个字节数组：

Finally, for the last method I used a Byte array:

Private Sub ByteArrayMethod(values() As Variant)
    Dim r As Long, c As Long, i As Long
    Dim chars() As Byte

    For r = LBound(values, 1) To UBound(values, 1)
         For c = LBound(values, 2) To UBound(values, 2)
            chars = values(r, c)
            values(r, c) = vbNullString
            For i = LBound(chars) To UBound(chars) Step 2
                If chars(i) > 47 And chars(i) < 58 Then
                    values(r, c) = values(r, c) & Chr$(chars(i))
                End If
            Next
         Next
    Next
End Sub

然后，我使用这个代码对1000个单元格进行基准测试，每个单元格包含25个字母和数字的随机组合：

Then I used this code to benchmark them against 1000 cells, each containing a random mix of 25 letters and numbers:

Private Sub Benchmark()
    Dim data() As Variant, start As Double, i As Long

    start = Timer
    For i = 1 To 5000
        data = ActiveSheet.Range("A1:J100").Value
        MidMethod data
    Next
    Debug.Print "Mid: " & Timer - start

    start = Timer
    For i = 1 To 5000
        data = ActiveSheet.Range("A1:J100").Value
        RegexMethod data
    Next
    Debug.Print "Regex: " & Timer - start

    start = Timer
    For i = 1 To 5000
        data = ActiveSheet.Range("A1:J100").Value
        ByteArrayMethod data
    Next
    Debug.Print "Byte(): " & Timer - start

End Sub

结果并不令人惊讶 - 正则表达式方法是 最快（但不是我所称的快速）：

The results weren't horribly surprising - the Regex method is by far the fastest (but none of them are what I'd call "fast"):

Mid: 24.3359375 
Regex: 8.31640625 
Byte(): 22.5625

请注意，我不知道如何与@ SiddharthRout的酷配方法进行比较，因为我无法通过我的测试工具来运行它。 www.extendoffice.com代码也可能仍在运行，所以我没有测试。

Note that I have no idea how this compares to @SiddharthRout's cool formula method in that I can't run it through my testing harness. The www.extendoffice.com code would also probably still be running, so I didn't test it.

这篇关于Excel VBA：从数字字段WITHOUT循环中删除非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！