Pulling specific text from Outllook emails to Excel and more

magrant · Jan 16, 2018

*This all Office 2013 on Windows 7*
I receive automated emails from an enrollment website and need to pull specific text from the Outlook emails into a spreadsheet. I’ve created a linked Microsoft Access Db to import the email information and then I export that into a Microsoft Excel sheet. After browsing through different sites, this is the formula I used to pull the specific information into the xlsx worksheet (example formula for column A <Name>):

=LEFT(MID(MySpreadSheet!D2,FIND(“Name: “,MySpreadSheet!D2)+6,LEN(MySpreadSheet!D2)),FIND(“* Email”,MID MySpreadSheet!D2,FIND(“Name: “,MySpreadSheet!D2)+6,LEN(MySpreadSheet!D2)))-5)

And I pull the info of the eight bulleted fields from the below email (this is the email format as well):

----------------------------------------------------------------------------------------------------
Dear School Representative,

The below applicant would like to enroll in your program:

* Name: Jane Doe
* Email: janedoe@gmail.com
* Phone: 555-555-5555
* Company: ABC
* Division: Sales
* Intro Course: Sales 101
* Years of Experience: 12
* Previous Date of Application: 1/16/2017

Please approve or disapprove the applicant’s request to join your program.

Regards,
THE School Board
----------------------------------------------------------------------------------------------------

The formula works beautifully until the last two columns (G&H), “Years of Experience” and “Previous Date of Application,” because not everyone has a previous application date. And if they don’t have a
“Previous Date of Application” then the last bullet doesn’t show up in the email. I can manually erase column H and adjust column G’s formula to this:

=LEFT(MID(MySpreadSheet!D2,FIND(“Experience: “,MySpreadSheet!D2)+12,LEN(MySpreadSheet!D2)),FIND(“Please approve”,MID MySpreadSheet!D2,FIND(“Experience: “,MySpreadSheet!D2)+6,LEN(MySpreadSheet!D2)))-11)

But that’s not very convenient with hundreds of records. So I’m looking for a better formula to pull the data and to magically recognize whena field isn’t present a place a defined value there instead (e.g., “N/A” if there’s no “previous date of application bullet” on the email).

Ultimately, I’d like it to output this way (sorry, tried to line up the columns):

A B C D E F G H

1 Jane Doe janedoe@gamil.com 555-555-5555 ABC Sales Sales 101 12 1/16/2017

2 Joe Gnu joegnu@gmail.com 555-555-5555 ABC Distro Distro 101 5 N/A

I am a novice Excel user and know just enough to break things. I appreciate any help you can throw my way. Thanks!

István Hirsch · Jan 17, 2018

Try this formula and layout. Enter the headings in bold into row 1. Then enter the formula in B2 and drag across and down:

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Name

Email

Phone

Company

Division

Intro Course

Years of Experience

Previous Date of Application

2

Dear School Representative,The below applicant would like to enroll in your program:* Name: Jane Doe* Email: janedoe@gmail.com* Phone: 555-555-5555* Company: ABC* Division: Sales* Intro Course: Sales 101* Years of Experience: 12* Previous Date of Application: 1/16/2017

Sheet1

magrant · Jan 18, 2018

Istvan,

Thank you for the recommendation. It almost worked.

The first time I used the formulas, neither cells H1 (Years of experience) or I1 (Previous Date of Application) filled and I couldn't copy the formulas down the sheet without strange formatting errors. I fixed the formatting errors by adding a "$" to the last part of the formula:

Ex.) =IFERROR(MID($A2&CHAR(10),FIND(B$1,$A2&CHAR(10))+LEN(B$1)+2,FIND(CHAR(10),$A2&CHAR(10),FIND(B$1,$A2&CHAR(10)))-FIND(B$1,$A2&CHAR(10))-LEN(B$1)-2),"")

With the fix, cells B-F work beautifully, but there are still quirks. For G, the formula pulls part of the email text (e.g., "ion: Sales" instead of just "Sales"). For H & I, there's no error code for me to work with; the cells just stay empty. Any thoughts?

Thanks again!

István Hirsch · Jan 18, 2018

You are correct. At the end of the formula I used B1 (twice) instead of B$1. That problem did not arise in the second row, but spoiled the ones below. After correcting I checked the formula downwards with another string and at me it seems correct.
In the row of headings you should enter just the same strings (Name, Email etc.) (and with the same capitalization!) that precedes the substrings to extract. The problem with "ion: Sales" may have occured because you had entered only "Div" in cell F1 instead of "Division", the formula has been written to work with the latter.
Please let me know if the formula works now as required.

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Name

Email

Phone

Company

Division

Intro Course

Years of Experience

Previous Date of Application

2

Dear School Representative,The below applicant would like to enroll in your program:* Name: Jane Doe* Email: janedoe@gmail.com* Phone: 555-555-5555* Company: ABC* Division: Sales* Intro Course: Sales 101* Years of Experience: 12* Previous Date of Application: 1/16/2017

3

Name: Joe GnuEmail: joegnu@gmail.comPhone: 555-555-5555Company: ABCDivision: DistroIntro Course: Distro 101Years of Experience: 5Previous Date of Application: N/A

Sheet1

magrant · Jan 18, 2018

Brilliant - it works! Thank you so much for your help!

István Hirsch · Jan 18, 2018

You are welcome.

Pulling specific text from Outllook emails to Excel and more

magrant

New Member

Excel Facts

István Hirsch

Well-known Member

magrant

New Member

István Hirsch

Well-known Member

magrant

New Member

István Hirsch

Well-known Member

Similar threads

Forum statistics

Share this page

Pulling specific text from Outllook emails to Excel and more

magrant

New Member

Excel Facts

István Hirsch

Well-known Member

magrant

New Member

István Hirsch

Well-known Member

magrant

New Member

István Hirsch

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock