Extracting phone number for each cell

[ABBOTTS04]

WHAT FORMULA WOULD WORK TO EXTACT JUST THE PHONE NUMBER FROM EACH CELL. IT STARTS WITH CELL A2

[TABLE="width: 1369"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117 [/TD]
[/TR]
</tbody>[/TABLE]

THANK YOU

 

[Rick Rothstein]

WHAT FORMULA WOULD WORK TO EXTACT JUST THE PHONE NUMBER FROM EACH CELL. IT STARTS WITH CELL A2

[TABLE="width: 1369"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117 [/TD]
[/TR]
</tbody>[/TABLE]

One example is not usually all that all that helpful in describing one's data. For example, is the phone number always at the end of the text? Is the phone number always shown without dashes or parentheses? Does the word "Phone" alway precede the phone number? Along with many other questions as well...

 

[lrobbo314]

Good points Rick. More examples would be helpful. But, if your example is a good representation of what all of your data looks like, then this should do it.

Function PHONE(s As String) As String
Dim RX As Object: Set RX = CreateObject("VBScript.RegExp")
Dim pat As String: pat = "\d{10}"


With RX
    .Pattern = pat
    .Global = True
    .MultiLine = True
    .ignorecase = True
    PHONE = .Execute(s)(0)
End With


End Function

 

[Rick Rothstein]

Good points Rick. More examples would be helpful. But, if your example is a good representation of what all of your data looks like, then this should do it.

Function PHONE(s As String) As String
Dim RX As Object: Set RX = CreateObject("VBScript.RegExp")
Dim pat As String: pat = "\d{10}"


With RX
    .Pattern = pat
    .Global = True
    .MultiLine = True
    .ignorecase = True
    PHONE = .Execute(s)(0)
End With


End Function

If you assume the example is a good representation, then I would suggest this function instead

Function PHONE(S As String) As String
  PHONE = Right(S, 10)
End Function

Hence, my questions.

 

[ABBOTTS04]

One example is not usually all that all that helpful in describing one's data. For example, is the phone number always at the end of the text? Is the phone number always shown without dashes or parentheses? Does the word "Phone" alway precede the phone number? Along with many other questions as well...

[TABLE="width: 1369"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117 [/TD]
[/TR]
[TR]
[TD]420 FOR THE PEOPLE COOPERATIVE INC : Stash Studio City : Email- 420forthepeople@gmail.com : Phone- 8188105353 [/TD]
[/TR]
[TR]
[TD]POINT BAY DISTRIBUTORS, LLC : Point Bay Distributors, LLC : Email- info@pointbaydist.com : Phone- 8505103483 : Website- https://www.pointbaydist.com/[/TD]
[/TR]
[TR]
[TD]KALI BOTANICALS : Kali Botanicals : Email- kalibotanicals213@gmail.com : Phone- 9512646666 [/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

[TABLE="width: 1369"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117 [/TD]
[/TR]
[TR]
[TD]420 FOR THE PEOPLE COOPERATIVE INC : Stash Studio City : Email- 420forthepeople@gmail.com : Phone- 8188105353 [/TD]
[/TR]
[TR]
[TD]POINT BAY DISTRIBUTORS, LLC : Point Bay Distributors, LLC : Email- info@pointbaydist.com : Phone- 8505103483 : Website- https://www.pointbaydist.com/[/TD]
[/TR]
[TR]
[TD]KALI BOTANICALS : Kali Botanicals : Email- kalibotanicals213@gmail.com : Phone- 9512646666 [/TD]
[/TR]
</tbody>[/TABLE]

It appears that your phone numbers are always 10 digits long with no dashes or parentheses and always follow the text "Phone- ". You could use the function that lrobbo314 posted, but another function that would also work is this...

Function PHONE(s As String) As String
  PHONE = Left(Split(s, "Phone- ")(1), 10)
End Function

 

[Michael M]

Maybe a straight formula

=MID(A1,FIND("Phone- ",A1)+7,10)

 

[Rick Rothstein]

maybe a straight formula

=mid(a1,find("phone- ",a1)+7,10)

good idea!!!


Edit Note: Odd... if you post all caps text, this forum changes it to all lower case.

 

[Michael M]

@Rick
I've never seen / noticed that before ???