从Excel中的单元格中提取数字 [英] Extract numbers from cell in Excel

问题描述

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

I've got a column where cells contain phone numbers in the following format:

To: [phone] Will Smith To: [phone] Tom Hopkins To: [phone] Julius Caesar

或

From: [phone] Jack Russel To: 112312414 Mr XYZ To: [phone] Hulk

我需要在单独的列中提取收件人的电话号码，这些名称不是必需的，例如

I need to extract the recipient phone numbers in a separate column, names not required e.g.

+ 6112312414，+ 61832892357，+ 447857747717 ，第二示例为 112312414，+ 61832892357 .有人可以帮忙吗?谢谢！

[phone], [phone], [phone] for the first example and 112312414, [phone] for the second example. Can someone please help with this? Thanks!

推荐答案

尝试以下用户定义功能:

Try the following User Defined Function:

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

这篇关于从Excel中的单元格中提取数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！