arrays - 使用数组和循环删除字符实例

Question

我有一个列，其中几乎每个单元格都由数字、字母和符号(“TS-403”或“TSM-7600”)的组合组成。我希望每个不是整数的字符都被删除/替换为空字符串，这样我就只剩下数字(“403”)。

我想到了两种方法:

我认为最好的方法是创建一个包含数字 0-9 的整数数组，然后使用 for 循环遍历单元格，其中如果单元格中的字符串包含 不是 在数组中，则应删除该符号(而不是整个单元格)。

Sub fixRequestNmrs()

Dim intArr() as Integer
ReDim intArr(1 to 10)

    For i = 0 to 9
    intArr(i) = i
    Next i


Dim bRange as Range
Set bRange = Sheets(1).Columns(2)

For Each cell in bRange.Cells
if cell.Value 
// if cell includes char that is not in the intArr, 
// then that char should be deleted/replaced.
...

End Sub()

也许第二种方法更简单，即使用 Split() 函数，因为“-”后面始终跟有数字，然后将第一个子字符串替换为“”。我对如何将 Split() 函数与范围和替换函数结合使用感到非常困惑......

For Each cell in bRange.Cells
Cells.Split(?, "-")
...

Answer 1

使用 Like 运算符将数字转换为整数

函数

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns an integer composed from the digits of a string.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function DigitsToInteger(ByVal SearchString As String) As Long

    Dim ResultString As String
    Dim Char As String
    Dim n As Long
    
    For n = 1 To Len(SearchString)
        Char = Mid(SearchString, n, 1)
        If Char Like "[0-9]" Then ResultString = ResultString & Char
    Next n
    
    If Len(ResultString) = 0 Then Exit Function

    DigitsToInteger = CLng(ResultString)

End Function

工作表示例

Sub DigitsToIntegerTEST()

    Const FIRST_ROW As Long = 2

    ' Read: Reference the (single-column) range.
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    If LastRow < FIRST_ROW Then Exit Sub ' no data
    
    Dim rg As Range: Set rg = ws.Range("B2", ws.Cells(LastRow, "B"))
    Dim rCount As Long: rCount = rg.Rows.Count
    
    ' Read: Return the values from the range in an array.
    
    Dim Data() As Variant
    
    If rCount = 1 Then
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else
        Data = rg.Value
    End If
    
    ' Modify: Use the function to replace the values with integers.
    
    Dim r As Long
    
    For r = 1 To rCount
        Data(r, 1) = DigitsToInteger(CStr(Data(r, 1)))
    Next r
    
    ' Write: Return the modifed values in the range.
    
    rg.Value = Data
    ' To test the results in the column adjacent to the right, instead use:
    'rg.Offset(, 1).Value = Data

End Sub

在 VBA 中(简单)

Sub DigitsToIntegerSimpleTest()
    Const S As String = "TSM-7600sdf"
    Debug.Print DigitsToInteger(S) ' Result 7600
End Sub

在 Excel 中

=DigitsToInteger(A1)