Excel formula tip

admin · 04-12-2009, 06:08:30 PM

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:

Code:

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

Copy it down all the rows...

Code:

 
=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 · 04-12-2009, 10:09:56 PM

very nice tip, thanks for that.

meinthecorner · 05-12-2009, 03:48:32 PM

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.

admin · 05-12-2009, 03:58:26 PM

Quote:

Originally Posted by 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.

Show off

what's wrong with Excel 97

Edwin · 06-12-2009, 01:56:58 AM

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 · 06-12-2009, 10:32:17 AM

grep is my friend

04-12-2009, 06:08:30 PM	#1 (permalink)
admin Administrator Join Date: Jun 2004 Posts: 8,517 No Classified Ratings Domain Trader Rating: (100% / 1)	Excel formula tip 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: Code: =IF(ISNUMBER(FIND(".co.uk",A1)),"UK", "") Copy it down all the rows... Code: =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 __________________ Domain Appraisal \| Domain Escrow Service \| UK Appliances \| Surrey Deals \| Domain Auctions from Acorn Domains Subscribe to our Domains For Sale newsletter :: Submit a Domain For Sale to our newsletter :: Domains For Sale

06-12-2009, 01:56:58 AM	#5 (permalink)
Edwin Join Date: Apr 2005 Location: Cambridge, UK Posts: 3,876 No Classified Ratings Domain Trader Rating: (100% / 46)	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. __________________ Memorable Domains Ltd - Over 7,000 descriptive, generic .co.uk domains for sale Please note: All sale prices over a week old are automatically invalid. No exceptions. Thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Domain Name Community	Replies	Last Post
Excel to Google Map	admin	General Board	2	06-08-2009 05:07:42 PM
Excel genius? anyone?	woopwoop	General Board	2	15-12-2008 09:02:44 PM

04-12-2009, 10:09:56 PM	#2 (permalink)
woopwoop Join Date: Jan 2007 Posts: 1,484 No Classified Ratings Domain Trader Rating: (100% / 15)	very nice tip, thanks for that.

05-12-2009, 03:48:32 PM	#3 (permalink)
meinthecorner Join Date: May 2005 Posts: 162 No Classified Ratings Domain Trader Rating: (100% / 1)	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.

06-12-2009, 10:32:17 AM	#6 (permalink)
davedevelopment Join Date: May 2009 Location: Brough, East Yorks Posts: 988 No Classified Ratings Domain Trader Rating: (100% / 59)	grep is my friend