Extract multiple sets of text from single cell

[Yvonne Love]

Good afternoon all!

We download data from a database and some of the data has a cell containing a single string of text, from which we would like to extract multiple sections. Here is an example of the data in the single string: District/ESC/Charter Name: Ingleside<br/>District/ESC/Charter Contact: Lynne Porter<br/>District/ESC/Charter Contact Email: lynne.porter@inglesideisd.org<br/>District/ESC/Charter Contact Phone: 3617767631<br/>Attendee: Name: Lynne Porter Email: lynne.porter@inglesideisd.org Phone: <br/>Attendee 2: Name: Danny Glover Email: danny.glover@inglesideisd.org Phone: <br/>Attendee 3: Name: Deborah Jones Email: deborah.jones@inglesideisd.org Phone:

We would like to be able to extract the Attendees' names and emails into separate cells so we can compile a registration name/email list. I know how to extract text before and after and in between characters, but I'm not sure how to, if it's possible, extract this data so we don't have to manually pull everything out. I've also attached a spreadsheet with literally this one cell of data (and a header). Is this possible? Without VBA or Power Query, if possible.

Thanks in advance! And happy holidays!
YL

 

[r2c2]

@Yvonne Love

VBA or PQ is the best way to solve this problem. But since you want a formula approach, here is one that works.

We just calculate the position of each person in the list using FIND formula like below.
For first person =FIND(": Name:",$A$2)
For subsequent persons =FIND(": Name:",$A$2, previous person position + 1)

Once we have the positions, we can use MID to extract the names & emails.
This assumes your data follows the pattern you have shown in the example.

See attached.

 

[Nebu]

Hi:

You can use the following code to extract email Ids

Sub getEmail()
Application.ScreenUpdating = False

Dim regEx: Set regEx = CreateObject("vbscript.regexp")
Dim i&: i = 2

For j& = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

strng$ = ActiveSheet.Range("A" & j).Value
With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b"
End With

Dim EMatches: Set EMatches = regEx.Execute(strng)
Dim EMatch

For Each EMatch In EMatches
    ActiveSheet.Cells(2, i) = EMatch.Value
    i = i + 1
Next

Next

Application.ScreenUpdating = True
End Sub

Go through the following link
http://stackoverflow.com/questions/...gex-in-microsoft-excel-both-in-cell-and-loops

To understand how to add reference library to VBA editor, It is important to add the reference library before running the macro.


The colored cells in the attached file will extract the names from email Ids using formulas.

Thanks

 

[Yvonne Love]

Great, thank you everyone! This is perfect!