Excel If statement

Using IF NOT in excel 2010

I have a column of domain names that I would like to separate by extension.

domain.com
domain11.co.uk
domina123.co
domain222.org.uk
domain6645.net

If I use  =IF(ISNUMBER(SEARCH(".co",A4)),A4) it will return the domain name Only if it contains .co and not .org.uk or.net however it also returns .co.uk and .com as they both contain .co as well!

How can I separate to JUST the .co or just the .org without the inclusions using some type of =if but NOT statement?

Many thanks
DexterstevensAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
=IF(RIGHT(A4,4)=".org"),A4,"")                   returns just cells ending in .org
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

so you only want the ones ending in .co ? If so, maybe try

=IF(RIGHT(A1,2)="co",A1,"")

cheers, teylyn
0
 
byundtCommented:
You can peel off just the com, co, uk or other domain using a formula like:
=TRIM(RIGHT(SUBSTITUTE(A1,".",REPT(" ",20)),4))

You can then sort by the results of that formula to get your list.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Thanks for the grade.

One more thought:

You could use text to columns with the . as the delimiter, then use Data > Filter to show just the rows with the extensions you want.
0
All Courses

From novice to tech pro — start learning today.