Extract all text AFTER any one of 7+ phrases in a lookup Range

[Akanddo]

I use this website regularly and absolutely love the contentand presentation and always learn a new thing or two.


I thought I would be able to write the formulas I need formy unique situation by browsing the Message Board and Learn Excel Resources,and even a Google search or 25, but to no avail.

If someone would be so kind as to either offer assistance orpoint me in the right direction, I’d be most obliged and grateful!

I have one Excel workbook with one worksheet with one columnof data.


The data is a copy/paste from a website describing collegegraduates' new employment updates.

As such, I the challenge is this.


I have a bunch of 'employer updates' text copied from the website ad pasted into Sheet 1, Col. A, with each row containing 1 person's data.


The first part of each cell is ALWAYS the person's name followed by: a space then a "(".


The gradation year is ALWAYS after that ( and formatted as: '##,


After the comma is the person's college major.


Ex: John Smith ('91, Accounting) was promoted to...




I already have my formulas to extract the name (cell B2)...

=TRIM(LEFT(A2,FIND("(",A2,1)-2))


...and grad year (cell C2)...

=TRIM(MID(A2,FIND("'",A2,1)+1,2))

but now I need their Position/Title in cell D2 (and for each row thereafter). The position/title is usually prefaced by an introductory phrase like:


has a new position as
has been named
has joined
is now a
is/has been promoted to
was named


I had originally put that block of phrase data in cells J2-J7 and selected them all to define them as Range "TITLE".


I thought I could use a formula to extract all text in cell A2 coming AFTER any of those phrases if found in the Range TITLE to create col. D with their position/title.

The output would look like this:

COL A COL B COL C COL D

John Smith ('91, Accounting) was John Smith '91 Associate Vice-President...(etc)
promoted to Associate Vice-President
for Landmark Technology.

I can't figure out a way to do this. Am I going about it the wrong way? Is there an easier solution I am missing?


Please advise, and thanks in advance for any assistance anyone can offer; it is MOST appreciated!


Regards,
Ky

 

[jtakw]

Hi,

Not sure if I'll be able to help, but need you to provide more sample data of various scenarios.
Also, after the Position/Title, your sample shows "for some company", is this to be included?

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Blurb​

[/td][td="bgcolor:#F3F3F3"]

Job​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Phrase​

[/td][td="bgcolor:#F3F3F3"]

Len​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]John Smith ('91, Accounting) was promoted to Associate Vice-President[/td][td="bgcolor:#CCFFCC"]Associate Vice-President[/td][td][/td][td]is now[/td][td]

6​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]John Smith ('91, Accounting) has a new position as Associate Vice-President[/td][td="bgcolor:#CCFFCC"]Associate Vice-President[/td][td][/td][td]is now a[/td][td]

8​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]John Smith ('91, Accounting) is now an Associate Vice-President[/td][td="bgcolor:#CCFFCC"]Associate Vice-President[/td][td][/td][td]is now an[/td][td]

9​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]John Smith ('91, Accounting) was named Associate Vice-President[/td][td="bgcolor:#CCFFCC"]Associate Vice-President[/td][td][/td][td]was named[/td][td]

9​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]John Smith ('91, Accounting) is now Associate Vice-President[/td][td="bgcolor:#CCFFCC"]Associate Vice-President[/td][td][/td][td]has joined[/td][td]

10​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][td]has been named[/td][td]

14​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][td]is promoted to[/td][td]

14​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td]was promoted to[/td][td]

15​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][td]has been promoted to[/td][td]

20​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td]has a new position as[/td][td]

21​

[/td][/tr]
[/table]

The formula in B2 is

=LOOKUP("zzz", MID(A2, FIND($D$2:$D$11, A2) + LEN($D$2:$D$11) + 1, 99))

Phrases are sorted by length.

 

[shg]

That wouldn't address the issue of a company name following.

 

[Akanddo]

See attached sample Excel file data for a few rows below. It's not exact and the phrases I listed are the most common, but not a finite list. The text following the name, year, and major also vary in content.

My purpose is to extract the name, grad year, position/title, and possibly employer name which follows, but I am not sure this can be done reliably or at all, so I might just have to do it manually (which takes a longer time).


Copied/Pasted Text from Website Name Extracted Grad Yr Extracted Position Company
[TABLE="width: 549"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]John Doe ('91, Accounting) was promoted to associate vice president of development at FSU. He has worked at FSU since 2011 and previously served as assistant vice president of development.[/TD]
[TD] John Doe[/TD]
[TD] 91[/TD]
[/TR]
[TR]
[TD]Sally Smith ('13, Finance) is now a senior consultant at Gov Relations, Inc. in Washington, D.C. and also is beginning law school. The former 25 Under 25 honoree began her career with Cooley Law Group and later worked as a senior operations analyst with U.S. Special Operations Command.[/TD]
[TD] Sally Smith[/TD]
[TD] 13[/TD]
[/TR]
[TR]
[TD]Bob Falter ('90, Accounting) was named partner and vice president of Finance for AECOM. A certified public accountant with 24 years of experience, Falter is a member of the Florida Institute of Certified Public Accountants.[/TD]
[TD] Bob Falter[/TD]
[TD] 90[/TD]
[/TR]
[TR]
[TD]Bill Willam ('82, Marketing) was promoted to senior vice president of National Sales for Keller Williams. Willam has spent over 30 years in the real estate industry and will oversee the sales and production areas within the company.[/TD]
[TD] Bill Willam[/TD]
[TD] 82[/TD]
[/TR]
[TR]
[TD]Rick Forest ('16, MAcc) has a new position as business analyst at The Charitable Hospital. He has moved to Ontario, Canada and recently received a post-graduate diploma in business intelligence systems infrastructure.[/TD]
[TD] Rick Forest[/TD]
[TD] 16[/TD]
[/TR]
[TR]
[TD]Humberto Halla ('15, Management, Marketing) has been promoted to senior product manager at Viacom. He has been with the company since 2015, beginning his career as a software intern.[/TD]
[TD] Humberto Halla[/TD]
[TD] 15[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

Not sure if I'll be able to help, but need you to provide more sample data of various scenarios.
Also, after the Position/Title, your sample shows "for some company", is this to be included?