If a certain string of text...Then ...

[chuckm4614]

Thanks in advance for the help and i hope this makes sense!

I have this info in a cell 06/04/2015: Forwarded candidate to jcantrell@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed

I need a formula that will find jcantrell and place the city that he works in in a different cell. So if H2 contains jcantrell - put "Atlanta" in cell J2. If H2 contains "rthompson" - Put Austin in J2....and so on
I should note that there are approximately 10 different names that could be in the H column.

Here's some of the data in that column:

06/04/2015: Forwarded candidate to jcantrell@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to rthompson@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to rthompson@tradestar.com; 06/04/2015: Forwarded candidate to lmosher@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to RLudvigsen@tradestar.com; 06/04/2015: Status changed from No Status to Withdrawn; 06/04/2015: Status changed from Withdrawn to Reviewed
06/04/2015: Forwarded candidate to jcantrell@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to jcantrell@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to cmcnair@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to lmosher@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed

 

[jardenp]

There's no way for us to determine which cities people are in.

Also, those look like real email addresses. You should consider using fake ones for posting.

 

[chuckm4614]

thanks for the reply... and yes - those are fake emails

I have the cities they are in:

if H2 contains jcantrell - put "Atlanta" in cell J2. If H2 contains "rthompson" - Put Austin in J2....and so on

 

[jardenp]

I'm sure you have the cities. I'm saying no one can help you figure this out if you don't share them.

 

[jardenp]

ADVERTISEMENT

The formula for finding the name in the first email of the text string is =MID([Target Cell],36,FIND("@",[Target Cell])-36). From that you could use VLOOKUP and a table of names and cities.

 

[chuckm4614]

=MID([Target Cell],36,FIND("@",[Target Cell])-36)

This will count over 36 characters before it starts looking in that field - Correct? is there a way to perform this same search if the email address isn't always 36 characters over?

 

[chuckm4614]

I'm still confused and am unable to make this work...

If I2 thru I5 contains:
06/04/2015: Forwarded candidate to jcantrell@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to jcantrell@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to cmcnair@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed
06/04/2015: Forwarded candidate to lmosher@tradestar.com; 06/04/2015: Status changed from No Status to Reviewed

and i have a table with the names and cities in Q2 thru Q13 & R2 thru R13

lmosher	HELEC
jcantrell	Atlanta
jcole	Houston
jsepulveda	Houston
mwagner	Houston
ashockley	Charlotte
jsaide	Dallas
cmcnair	San Antonio
rthompson	Austin
lreed	Tampa
mwest	HELEC
rludvigsen	Houston

<colgroup><col width="65" span="2" style="width: 65pt;"></colgroup><tbody>
</tbody>

How do i search for jcantrell and put Atlanta in O2?

Thanks again and I apologize for being a pest!

 

[Aladin Akyurek]

In O2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($Q$2:$Q$13,$I2),$R$2:$R$13)