使用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)

如果您有CONCAT函数，则可以使用一个相对简单的公式来完成此操作--不需要VBA：

=CONCAT(IFERROR(--MID(A1,SEQUENCE(LEN(A1)),1),""))

如果您更喜欢早期版本的Excel中的非VBA解决方案，则可以使用更复杂的公式，但我必须返回到我的文件中才能找到它。

一个复杂的函数GetVal()

以下功能

• 通过帮助函数String2Arr()将字符串转换为单个字符数组arr
• 通过巧妙地执行Application.Match（这里没有第三个参数，该参数主要用于精确搜索，并通过比较两个数组），将它们隔离为数字（类别代码6）或非数字类别（其他）
• 通过Instr()查找原始字符串中的起始位置
• 通过Val()返回右子字符串的值（~〉参见注解）。

Function GetVal(ByVal s As String) As Double
    Dim arr:      arr = String2Arr(s):      Debug.Print Join(arr, "|")
    Dim chars:    chars = Split(" ,',+,-,.,0,A", ",")
    Dim catCodes: catCodes = Application.Match(arr, chars)  'No 3rd zero-argument!!
    Dim tmp$:     tmp = Join(catCodes, ""): Debug.Print Join(catCodes, "|")
    Dim pos&:     pos = InStr(tmp, 6)   ' Pos 6: Digits; pos 1-5,7: other symbols/chars
    GetVal = Val(Mid(s, pos))           ' calculate value of right substring
End Function

备注

Val函数可以将带有起始数字的（子）字符串转换为数字，即使后面有非数字字符。

• 帮助函数String2Arr() *

将字符串原子化为单个字符数组：

Function String2Arr(ByVal s As String)
    s = StrConv(s, vbUnicode)
    String2Arr = Split(s, vbNullChar, Len(s) \ 2)
End Function

示例呼叫

Dim s As String
    s = "aA+*&$%(y#,'/\)!-12034.56blabla"
    Debug.Print GetVal(s)          ' ~~> 12034.56