Dec 04 2018
07:54 AM
- last edited on
Jul 12 2019
11:17 AM
by
TechCommunityAP
Dec 04 2018
07:54 AM
- last edited on
Jul 12 2019
11:17 AM
by
TechCommunityAP
I need a formula to help me look at a column of email addresses.
There are many agencies using the same email extension.
For example:
jsmith@nypd.nyc.gov
aclark@FDNY.nyc.gov
jdoe@schools.nyc.gov
aapple@mayor.nyc.gov
Let's say that NYPD's agency code is 1
FDNY is 2
Schools is 3
and Mayor is 4.
I want to look at column C, which has the email addresses. Let's say there are 250 email addresses in total.
Each agency has several employees. In column D, I would like to display the agency code of the agency.
This way, I can then group all the email addresses with code 1, all the emails with code 2, etc.
Thank you in advance.
Dec 04 2018 08:18 AM
Hi Renne,
You need to a lookup table to get the code name form it.
Once you have such a table, you can use a lookup formula such as VLOOKUP to get what you want.
Here's an example
The formula in cell D2:
=VLOOKUP(MID(C2,SEARCH("@",C2)+1,SEARCH(".",C2)-SEARCH("@",C2)-1),$J$4:$K$7,2,0)
Hope that helps
Dec 04 2018 03:58 PM
If the only goal is to group emails by agencies when Pivot Table works - extract agencies names and pivot then.