extract email address and phone number

[RDH2022]

Hi there, I am trying to extract email addresses, phone numbers and names from line items in Excel. There are no spacing. It's just one long string, is there a way to do it?
Results to show: Debra Hill 213-225-3576 hill13503@sbcglobal.net in different columns

example:

Debra HillManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong

 

[Peter_SSs]

Welcome to the MrExcel board!

Are your phone numbers always 10 digits with dashes like in that example?
Is the email address always followed by a date?

Hard to tell from one example only. What about 5-10 varied examples and the expected results with XL2BB?

 

[RDH2022]

Welcome to the MrExcel board!

Are your phone numbers always 10 digits with dashes like in that example?
Is the email address always followed by a date?

Hard to tell from one example only. What about 5-10 varied examples and the expected results with XL2BB?

Yes always like that with the same exact format. Only thing that changes are the values.

 

[kweaver]

I don't know how you'll separate the name from the title, but this should handle the phone and email, I think:

temporary.xlsm
	B	C
1	Phone	email
2	213-225-3576	hill13503@sbcglobal.net
Sheet6

Cell Formulas
Range	Formula
B2	B2	=MID(A2,SEARCH("???-",A2),12)
C2	C2	=MID(A2,SEARCH("???-",A2)+12,SEARCH("??/",A2)-(SEARCH("???-",A2)+12))

 

[kweaver]

This might do it for the name (assumes name, first and last, and title are all in proper case with 1st char being uppercase)::

=LEFT(A2,SMALL(IFERROR(FIND(CHAR(ROW($65:$90)),A2,2),""),2)-1)

 

[RDH2022]

This might do it for the name (assumes name, first and last, and title are all in proper case with 1st char being uppercase)::

=LEFT(A2,SMALL(IFERROR(FIND(CHAR(ROW($65:$90)),A2,2),""),2)-1)

This helped with the name! thank you!!

 

[Peter_SSs]

Unfortunately, we never got the 5-10 varied examples that I asked for so I don't really know what your data might look like but I suspect that the formulas suggested could turn up some strange results - see the coloured cells in the first section of the mini-sheet below where I have used those suggested formulas.

If any of those name circumstances could occur in your data then I suggest the following user-defined functions as an alternative approach, with results shown in the second section below.
Post back if you think this might be useful but are not sure how to implement UDFs
My assumption with the functions is that the persons position (eg Manager) is always one-word consisting of an upper-case letter followed by a number of lower-case letters with no special characters (eg hyphens). If that is not the case then please give details of what the positions could be.

Function fPhone(s As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\d{3}\-\d{3}\-\d{4}"
  fPhone = RX.Execute(s)(0)
End Function

Function fEmail(s As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(\d{3}\-\d{3}\-\d{4})(.+?)(?=\d{2}\/)"
  fEmail = RX.Execute(s)(0).SubMatches(1)
End Function

Function fName(s As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^.+)(?=[A-Z][^A-Z]+\d{3}\-\d{3}\-\d{4})"
  fName = RX.Execute(s)(0)
End Function

RDH2022.xlsm
	A	B	C	D
1		Phone	email	Name
2	Debra HillManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	213-225-3576	hill13503@sbcglobal.net	Debra Hill
3	John McDonaldManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	213-225-3576	hill13503@sbcglobal.net	John Mc
4	Debra Hill-JonesManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	ill-JonesMan	ager213-225-3576hill13503@sbcglobal.net	Debra Hill-
5	Mary-Lou JonesManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	ary-Lou Jone	sManager213-225-3576hill13503@sbcglobal.net	Mary-Lou
6
7		Phone	email	Name
8	Debra HillManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	213-225-3576	hill13503@sbcglobal.net	Debra Hill
9	John McDonaldManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	213-225-3576	hill13503@sbcglobal.net	John McDonald
10	Debra Hill-JonesManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	213-225-3576	hill13503@sbcglobal.net	Debra Hill-Jones
11	Mary-Lou JonesManager213-225-3576hill13503@sbcglobal.net05/30/2018, 9:53 AMJane Yong	213-225-3576	hill13503@sbcglobal.net	Mary-Lou Jones
Split Text

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,SEARCH("???-",A2),12)
C2:C5	C2	=MID(A2,SEARCH("???-",A2)+12,SEARCH("??/",A2)-(SEARCH("???-",A2)+12))
D2:D5	D2	=LEFT(A2,SMALL(IFERROR(FIND(CHAR(ROW($64:$89)),A2,2),""),2)-1)
B8:B11	B8	=fPhone(A8)
C8:C11	C8	=fEmail(A8)
D8:D11	D8	=fName(A8)