Extract Email Address from Cell with Multiple Data

[mblackok]

I am exporting a file of data and need to extract the work email address from each cell that contains user details including personal email address. This action will need to be completed each day and I am wondering if there is a formula that will ignore the "personal email" field and extract only the "email" field needed?

 

[Bosquedeguate]

When a Email (Not Personal Email) is found what should be done with it?

How are you “extracting” these email addresses? On a case-by-case basis or do you want an entire sheet or selected range tested and extract emails.

A little more clarification would be helpful.

If multiple email addresses are “extracted” the code will have to be a Macro (VBA Sub) and executed from the View menu, a button on the sheet, …

 

[Fluff]

Hi & welcome to MrExcel.
It's very difficult to tell what is in the cell from an image, but try

=TEXTBEFORE(TEXTAFTER(D2,"Email: ",-1)," ")

 

[mblackok]

Sorry about that. I tried to use the plugin but I wasn't able to get it to work. The formula you provided is so close! The desired email was extracted but the results included the word Department with a colon after.

ddi@collegeprep.org
Department:

 

[mblackok]

When a Email (Not Personal Email) is found what should be done with it?

How are you “extracting” these email addresses? On a case-by-case basis or do you want an entire sheet or selected range tested and extract emails.

A little more clarification would be helpful.

If multiple email addresses are “extracted” the code will have to be a Macro (VBA Sub) and executed from the View menu, a button on the sheet, …

The daily project I will manage includes two files of accounts that need to be created and accounts that have been created. These files are exported from two different programs and the unique identifier is the "Email:" address. So, I am thinking I need to extract the "Email:" from the cell so that I can use VLookup to identify which accounts have been created and which have not. The only email address I need is the "Email:" address as the "Personal Email:" address would return a null value and likely falsely indicate an account has not been created.
I am definitely open to suggestions! Thank you for taking a look!

 

[Dave Patton]

One quick idea based on the limited information.

T202307a.xlsm
	D	E	F
1
2	Personal Email: ddi@xxxxcollege.orgDepartment 123	ddi@xxxxcollege.orgDepartment
3	Personal Email: ddi@xxxxcollege.orgDepartment 123	ddi@xxxxcollege.org
4
1f

Cell Formulas
Range	Formula
E2	E2	=TEXTBEFORE(TEXTAFTER(D2,"Email: ",-1)," ")
E3	E3	=LET(x,TEXTAFTER(D3,"Email: ",-1),LEFT(x,FIND(".",x,1)+3))

 

[Fluff]

How about

=TEXTBEFORE(TEXTAFTER(D2,"Email: ",-1),CHAR(10))

 

[mblackok]

How about

=TEXTBEFORE(TEXTAFTER(D2,"Email: ",-1),CHAR(10))

This worked beautifully! I pulled the entire report and used this formula and it worked for multiple domain email addresses. Thank you so much!

 

[mblackok]

@Fluff @Dave Patton @Bosquedeguate Thank you so much for taking the time to read my question and try to help. I was able to complete the task at hand and know that as I run these daily reports, the formula =LET(x,TEXTAFTER(D3,"Email: ",-1),LEFT(x,FIND(".",x,1)+3)) will be such a time saver than text to cell and then search or concatenate back etc. Thank you, thank you, thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback.
Note that the formula in post#9 will not work for domains that have multiple . such as .co.uk