海量数据去除非数字字符（四种方法）

如果在一列中存入不规则的海量数据，数字和字符串混合，现在需要提取数字，总共总结出下面四中方法，依据运行速度快慢依次排列，推荐使用前三种方法：

第一种：数组方法，速度最快

1 Sub y3231057_1()
2 t = Timer
3 Dim Arr, i & , j%, k & , S$, SS$, Str $
4 Dim Arr1() As Single
5 Arr = Range( " A1:A " & [a65536].End(xlUp).Row)
6 k = UBound (Arr)
7 ReDim Arr1( 1 To k, 1 To 1 )
8 For i = 1 To k
9 S = Arr(i, 1 )
10 For j = 1 To Len (S)
11 SS = Mid (S, j, 1 )
12 If SS Like " [0-9.] " Then
13 Str = Str & SS
14 Next j
15 Arr1(i, 1 ) = Str
16 Str = ""
17 Next i
18 [B1].Resize(k, 1 ) = Arr1
19 [d65536].End(xlUp).Offset( 1 , 0 ) = Timer - t
20   End Sub

第二种：正则表达式的方法，速度较数组方法稍慢

1 Sub y3231057_2()
2 t = Timer
3 Dim Arr, i &
4 Dim Arr1() As Single
5 Arr = Range( " A1:A " & [a65536].End(xlUp).Row)
6 k = UBound (Arr)
7 ReDim Arr1( 1 To k, 1 To 1 )
8 With CreateObject ( " VBSCRIPT.REGEXP " )
9 For i = 1 To k
10 .Pattern = " 你|是|限制|大小|自动|应用|以下 "
11 .Global = True
12 Arr1(i, 1 ) = .Replace(Arr(i, 1 ), "" )
13 Next i
14 End With
15 [B1].Resize(k, 1 ) = Arr1
16 [f65536].End(xlUp).Offset( 1 , 0 ) = Timer - t
17   End Sub

第三种：遍历需转换字符，依据需转换字符批量转换方法，速度叫正则表达式方法稍慢

1 Sub y3231057_3()
2 t = Timer
3 Dim Arr
4 Dim k As Integer
5 Dim rng As Range
6 Application.ScreenUpdating = False
7 Arr = Array( " 你 " , " 是 " , " 限制 " , " 大小 " , " 自动 " , " 应用 " , " 以下 " )
8 Set rng = Range( " C1:C " & Sheet1.[C65536].End(xlUp).Row)
9 For k = 0 To UBound (Arr)
10 rng.Replace what: = Arr(k), replacement: = ""
11 Next
12 Set rng = Nothing
13 Application.ScreenUpdating = True
14 [g65536].End(xlUp).Offset( 1 , 0 ) = Timer - t
15   End Sub

第四种：依据单元格循环，一个一个转换方法，速度很慢，约为上面几种方法用时的几百倍

1 Sub y3231057_4()
2 t = Timer
3 Dim Arr
4 Dim i As Long
5 Dim k As Integer
6 Arr = Array( " 你 " , " 是 " , " 限制 " , " 大小 " , " 自动 " , " 应用 " , " 以下 " )
7 For i = 1 To [a65536].End(xlUp).Row
8 For k = 0 To UBound (Arr)
9 Cells(i, " C " ).Replace Arr(k), ""
10 Next
11 Next
12 [e65536].End(xlUp).Offset( 1 , 0 ) = Timer - t
13   End Sub

转载于:https://www.cnblogs.com/leolis/archive/2011/07/08/2101154.html