Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 895
  • Last Modified:

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
0
Dexterstevens
Asked:
Dexterstevens
  • 2
  • 2
2 Solutions
 
byundtCommented:
=IF(RIGHT(A4,4)=".org"),A4,"")                   returns just cells ending in .org
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now