excel - 从Excel中的单元格中提取数字

Question

我有一列单元格包含以下格式的电话号码:

To: +6112312414 Will Smith To: +61832892357 Tom Hopkins To: +447857747717 Julius Caesar

或者

From: +44712423110 Jack Russel To: 112312414 Mr XYZ To: +61832892357 Hulk

我需要在单独的列中提取收件人电话号码，不需要姓名，例如
+6112312414, +61832892357, +447857747717对于第一个示例和 112312414, +61832892357对于第二个例子。有人可以帮忙吗？谢谢!

Answer 1

尝试以下用户定义函数:

Public Function PhoneList(st As String) As String
    Dim v As String
    PhoneList = ""
    v = Replace(st, "To: ", Chr(1))
    arr = Split(v, " ")
    For Each a In arr
        If InStr(a, Chr(1)) > 0 Then
            PhoneList = PhoneList & ", " & a
        End If
    Next a
    PhoneList = Replace(PhoneList, Chr(1), "")
    PhoneList = Mid(PhoneList, 3, 9999)
End Function