asked on

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

pjevin

a. =IF(D2="","True","False")
b. =IF(ISNUMBER(FIND("@us.army.mil",D2)),"true","false")
c. =IF(ISNUMBER(FIND("@mail.mil",D2)),"true","false")

Chris Miller

ASKER

ok, sorry where do I put them.

Saqib Husain

Do you have to do this again and again or is this a one-time exercise?

Chris Miller

ASKER

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.

pjevin

You could put it as column e in each tab and then filter (data->filter->autofilter) by column e being "true"

Chris Miller

ASKER

I tried that it doesnt seem to work.

pjevin

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.

Chris Miller

ASKER

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.

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Miller

ASKER

thanks, thats what I needed.