Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-294695%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294695%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20to%20help%20me%20look%20at%20a%20column%20of%20email%20addresses.%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20agencies%20using%20the%20same%20email%20extension.%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3Ejsmith%40nypd.nyc.gov%3C%2FP%3E%3CP%3Eaclark%40FDNY.nyc.gov%3C%2FP%3E%3CP%3Ejdoe%40schools.nyc.gov%3C%2FP%3E%3CP%3Eaapple%40mayor.nyc.gov%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20say%20that%20NYPD's%20agency%20code%20is%201%3C%2FP%3E%3CP%3EFDNY%20is%202%3C%2FP%3E%3CP%3ESchools%20is%203%3C%2FP%3E%3CP%3Eand%20Mayor%20is%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20look%20at%20column%20C%2C%20which%20has%20the%20email%20addresses.%26nbsp%3B%20Let's%20say%20there%20are%20250%20email%20addresses%20in%20total.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20agency%20has%20several%20employees.%26nbsp%3B%20In%20column%20D%2C%20I%20would%20like%20to%20display%20the%20agency%20code%20of%20the%20agency.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20way%2C%20I%20can%20then%20group%20all%20the%20email%20addresses%20with%20code%201%2C%20all%20the%20emails%20with%20code%202%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-294695%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-295008%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-295008%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20the%20only%20goal%20is%20to%20group%20emails%20by%20agencies%20when%20Pivot%20Table%20works%20-%20extract%20agencies%20names%20and%20pivot%20then.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-294716%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-294716%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Renne%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20a%20lookup%20table%20to%20get%20the%20code%20name%20form%20it.%3C%2FP%3E%3CP%3EOnce%20you%20have%20such%20a%20table%2C%20you%20can%20use%20a%20lookup%20formula%20such%20as%20VLOOKUP%20to%20get%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHere's%20an%20example%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20in%20cell%20D2%3A%3C%2FP%3E%3CPRE%3E%3DVLOOKUP(MID(C2%2CSEARCH(%22%40%22%2CC2)%2B1%2CSEARCH(%22.%22%2CC2)-SEARCH(%22%40%22%2CC2)-1)%2C%24J%244%3A%24K%247%2C2%2C0)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F61301iD30AC4DDE512FBE5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222018-12-04_18-16-39.png%22%20title%3D%222018-12-04_18-16-39.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Renee Schweke
Occasional 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies