Excel formula help

Copper Contributor

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.

 

2 Replies

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)

 2018-12-04_18-16-39.png

 

 

Hope that helps

If the only goal is to group emails by agencies when Pivot Table works - extract agencies names and pivot then.