Excel formula tip

[AdminAdmin is verified member.]

Scenario:

You have a text file list of domains, all types of TLDs but you want to sort them to find just the .co.uk ones

Open / import the list in Excel
I'll assume the domain list is in column A
In column B, add the following formula:

=IF(ISNUMBER(FIND(".co.uk",A1)),"UK", "")

Copy it down all the rows...

=IF(ISNUMBER(FIND(".co.uk",A2)),"UK", "")
=IF(ISNUMBER(FIND(".co.uk",A3)),"UK", "")

If the domain name is a .co.uk, column B will state "UK" else nothing
Change the "co.uk" part of the formula if you want to identify another extension

Sort both columns by column B then you have a list of just the UK domains!

Enjoy

Admin

 

[woopwoop]

very nice tip, thanks for that.

 

[meinthecorner]

In Excel 07, you could highlight the column and use a Text filter containing the text .co.uk or the tld you wish to filter.

 

[AdminAdmin is verified member.]

In Excel 07, you could highlight the column and use a Text filter containing the text .co.uk or the tld you wish to filter.

Show off what's wrong with Excel 97

 

[EdwinEdwin is verified member.]

You could also use =find(".co.uk",a1) in cell b1 (assuming that you have your list of domains in column a, starting at cell a1. Then copy that formula into all the column b cells that have domains in column a, and sort column b using the sort function.

 

[davedevelopment]

grep is my friend