Excel match text in a column

I have four columns, A=Name, B=Type, C=Unit, D=Email Address

I need a tab that shows A,B,C,D columns based on D being blank (no email address in the column)

I need a tab that shows A,B,C,D columns based on D having an email address that ends in @us.army.mil

I need a tab that shows A,B,C,D columns based on D having an email address that ends in @mail.mil
CMILLERAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Select your data (columns A to D)
From the ribbon menu select    Data > Filter
From the dropdown on column D
    Uncheck All
    Check blanks
    Click OK
Now you have a list with blanks in column D
Copy this list to a new sheet

Now from the dropdown on column D
    Select Text filters
    Select contains
    Type @us.army.mil
    Click OK
Now you have a list with @us.army.mil in column D
Copy this list to a new sheet

Do the same with the other address
0
 
pjevinCommented:
a. =IF(D2="","True","False")
b. =IF(ISNUMBER(FIND("@us.army.mil",D2)),"true","false")
c. =IF(ISNUMBER(FIND("@mail.mil",D2)),"true","false")
0
 
CMILLERAuthor Commented:
ok, sorry where do I put them.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Saqib Husain, SyedEngineerCommented:
Do you have to do this again and again or is this a one-time exercise?
0
 
CMILLERAuthor Commented:
one time. I already have tabs sorted by unit and type. I just need to know if column D is blank or has an email address of Name@us.army.mil or name@mail.mil.
0
 
pjevinCommented:
You could put it as column e in each tab and then filter (data->filter->autofilter) by column e being "true"
0
 
CMILLERAuthor Commented:
I tried that it doesnt seem to work.
0
 
pjevinCommented:
If you copied one of those 3 expressions into e2, then copied e2 into all the cells needed for the sheet it should tell you if it's true or false for each, then apply the autofilter and select "true" from the drop down to only display the ones that are true.  Repeat in other tabs for the other sections.
0
 
CMILLERAuthor Commented:
it didnt. I used =IF(ISNUMBER(FIND("@mail.mil",D2)),"true","false") for e2-e641 and it returned false for all. so, if it finds an email address with either @mail.mil or @us.army.mil it reports false.
0
 
CMILLERAuthor Commented:
thanks, thats what I needed.
0
All Courses

From novice to tech pro — start learning today.