Extracting phone number for each cell

ABBOTTS04

New Member
Joined
Jul 22, 2019
Messages
2
WHAT FORMULA WOULD WORK TO EXTACT JUST THE PHONE NUMBER FROM EACH CELL. IT STARTS WITH CELL A2

WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117

<colgroup><col></colgroup><tbody>
</tbody>

THANK YOU
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,947
Office Version
2010
Platform
Windows
WHAT FORMULA WOULD WORK TO EXTACT JUST THE PHONE NUMBER FROM EACH CELL. IT STARTS WITH CELL A2

WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117

<colgroup><col></colgroup><tbody>
</tbody>
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...
 
Last edited:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
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.

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,947
Office Version
2010
Platform
Windows
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.

Code:
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 :devilish:
Code:
Function PHONE(S As String) As String
  PHONE = Right(S, 10)
End Function
Hence, my questions.
 

ABBOTTS04

New Member
Joined
Jul 22, 2019
Messages
2
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...
WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117
420 FOR THE PEOPLE COOPERATIVE INC : Stash Studio City : Email- 420forthepeople@gmail.com : Phone- 8188105353
POINT BAY DISTRIBUTORS, LLC : Point Bay Distributors, LLC : Email- info@pointbaydist.com : Phone- 8505103483 : Website- https://www.pointbaydist.com/
KALI BOTANICALS : Kali Botanicals : Email- kalibotanicals213@gmail.com : Phone- 9512646666

<colgroup><col></colgroup><tbody>
</tbody>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,947
Office Version
2010
Platform
Windows
WCW ORGANIZATION INC. : Email- rooz51@gmail.com : Phone- 3108455117
420 FOR THE PEOPLE COOPERATIVE INC : Stash Studio City : Email- 420forthepeople@gmail.com : Phone- 8188105353
POINT BAY DISTRIBUTORS, LLC : Point Bay Distributors, LLC : Email- info@pointbaydist.com : Phone- 8505103483 : Website- https://www.pointbaydist.com/
KALI BOTANICALS : Kali Botanicals : Email- kalibotanicals213@gmail.com : Phone- 9512646666

<colgroup><col></colgroup><tbody>
</tbody>
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...
Code:
Function PHONE(s As String) As String
  PHONE = Left(Split(s, "Phone- ")(1), 10)
End Function
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
Maybe a straight formula

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,947
Office Version
2010
Platform
Windows
maybe a straight formula

Code:
=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.:confused:
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,889
Office Version
2013
Platform
Windows
@Rick
I've never seen / noticed that before ???
 

Watch MrExcel Video

Forum statistics

Threads
1,099,787
Messages
5,470,781
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top