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

chuckm4614

New Member
Joined
Jun 12, 2015
Messages
4
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
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
 
Upvote 0
I'm sure you have the cities. I'm saying no one can help you figure this out if you don't share them.
 
Upvote 0
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.
 
Upvote 0
=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?
 
Upvote 0
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
lmosherHELEC
jcantrellAtlanta
jcoleHouston
jsepulvedaHouston
mwagnerHouston
ashockleyCharlotte
jsaideDallas
cmcnairSan Antonio
rthompsonAustin
lreedTampa
mwestHELEC
rludvigsenHouston

<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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top