Excel problem

[vetran]

real men use AWK!

[Spacecadet]

Bit late but this formula would have helped:

=IF(LEN(A1)-IFERROR(FIND(".co.uk",A1),0)=5, ".co.uk","")

[Cliphead]

Thanks all for the help. Job done and newsletter (spam ), on the way.

[malvolio]

...erm you use custom auto filter > ends with
No need to do paste special either, just filter, highlight and paste

Excel 2010

Precisely. Paste Special was only needed if the original data was a formula rather than text.

@cliphead - wasn't trying to be clever, but just wanted to know where you were going before spending too much time on it.

[AtW]

I have list of email addresses but want to filter just those ending with .uk. Any ideas?

Copy/paste to flat file named emails.txt

In command line -

find ".uk" emails.txt >ukemails.txt

Copy/paste back to Excel.

HTH

[administrator]

...erm you use custom auto filter > ends with
No need to do paste special either, just filter, highlight and paste

Excel 2010

Yup, just tried and works a treat. Highlight the column, from the Data tab choose Filter, click the little drop down that appears at the top of the column, select Text Filters, click Ends With and type in UK.

Very nice!

[Olly]

[email protected] - so therefore will not be fit for purpose for the task in hand.

...erm you use custom auto filter > ends with
No need to do paste special either, just filter, highlight and paste

Excel 2010

[Cliphead]

Thanks Admin I'll try that.

Mal, just need to discard any email address that isn't from the UK but finding it difficult to filter / sort. A one off operation, once I have all the UK based addresses I can import them into MySQL.

[administrator]

Does he want a filtered list, or an extract of the original? Repeat operation or one-off? Manual or automated? Source data is pure data or a formula?

A simple extract would be a custom filter (select all records containing ".uk") then cut and paste the resultant list using Paste Special/Values only. That' probably not what we;re looking for though...

Many answers. first you need a problem statement.

I think you are being too demanding in your requirement seeking. The requirement is in the OP.

I have list of email addresses but want to filter just those ending with .uk.

The solution I have shown leaves the original data in a column and the extra ones can be deleted out once you have sorted your list and extracted the data you want.

Your solution would match anyone with a .uk in the first portion of their addess e.g. [email protected] - so therefore will not be fit for purpose for the task in hand.

[malvolio]

Does he want a filtered list, or an extract of the original? Repeat operation or one-off? Manual or automated? Source data is pure data or a formula?

A simple extract would be a custom filter (select all records containing ".uk") then cut and paste the resultant list using Paste Special/Values only. That' probably not what we;re looking for though...

Many answers. first you need a problem statement.

[administrator]

I have list of email addresses but want to filter just those ending with .uk. Any ideas?

Thanks for the help Mal

I am no excel guru and most of the stuff I do is hacky but you could try copying the email column to a new column. Now click on the column to highlight all cells, click the Data tab and choose "Text to Columns" on the ribbon. Choose Delimited and click next, put a tick in "other" and put the @ symbol in, clik Next, click Finish.

This will then give you a new column with the bit after the @, again click on the top of the column to select all the domain name and extensions and click "Text to Columns" again. Delimited should be checked so click Next, now replace the @ with a full stop, click Next click Finish.

Now you will have two new columns, one with the initial part of any double part domain extentions and one with the second. All official UK extensions are double spaced names, .co.uk, .org.uk, .me.uk, .gov.uk etc. You may have the odd .uk.com but they aren't real UK extensions administered by Nominet so depending on what you are doing you might want a second sort to select those or you might not...

Now that you have all the uk extension parts in one column you can sort on that column and just select the UK ones...

Worked for me on a small dataset.

I am sure some smart arse will come along with some code to do it in a sec but that might be enough to get you through for now...

[malvolio]

I have list of email addresses but want to filter just those ending with .uk. Any ideas?

Lots, thanks.

I assume you've used custom fileters and/or compound commands using the lookup functions and/or Indirect commands. But it rather depends what you're trying to do, doesn't it? If you could work up the time and energy to explain the problem, it may be possible to come up with an answer.

[Cliphead]

I have list of email addresses but want to filter just those ending with .uk. Any ideas?