I have list of email addresses but want to filter just those ending with .uk. Any ideas?
- Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
- Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Excel problem
Collapse
X
-
-
Originally posted by Cliphead View PostI have list of email addresses but want to filter just those ending with .uk. Any ideas?
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.Blog? What blog...? -
Originally posted by Cliphead View PostI have list of email addresses but want to filter just those ending with .uk. Any ideas?
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...Comment
-
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.Blog? What blog...?Comment
-
Originally posted by malvolio View PostDoes 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 have list of email addresses but want to filter just those ending with .uk.
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.Comment
-
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.Comment
-
Originally posted by administrator View Post[email protected] - so therefore will not be fit for purpose for the task in hand.
No need to do paste special either, just filter, highlight and paste
Excel 2010Comment
-
Originally posted by Olly View Post...erm you use custom auto filter > ends with
No need to do paste special either, just filter, highlight and paste
Excel 2010
Very nice!Comment
-
Originally posted by Cliphead View PostI have list of email addresses but want to filter just those ending with .uk. Any ideas?
In command line -
find ".uk" emails.txt >ukemails.txt
Copy/paste back to Excel.
HTHComment
-
Originally posted by Olly View Post...erm you use custom auto filter > ends with
No need to do paste special either, just filter, highlight and paste
Excel 2010
@cliphead - wasn't trying to be clever, but just wanted to know where you were going before spending too much time on it.Blog? What blog...?Comment
- Home
- News & Features
- First Timers
- IR35 / S660 / BN66
- Employee Benefit Trusts
- Agency Workers Regulations
- MSC Legislation
- Limited Companies
- Dividends
- Umbrella Company
- VAT / Flat Rate VAT
- Job News & Guides
- Money News & Guides
- Guide to Contracts
- Successful Contracting
- Contracting Overseas
- Contractor Calculators
- MVL
- Contractor Expenses
Advertisers
Contractor Services
CUK News
- Streamline Your Retirement with iSIPP: A Solution for Contractor Pensions Sep 1 09:13
- Making the most of pension lump sums: overview for contractors Sep 1 08:36
- Umbrella company tribunal cases are opening up; are your wages subject to unlawful deductions, too? Aug 31 08:38
- Contractors, relabelling 'labour' as 'services' to appear 'fully contracted out' won't dupe IR35 inspectors Aug 31 08:30
- How often does HMRC check tax returns? Aug 30 08:27
- Work-life balance as an IT contractor: 5 top tips from a tech recruiter Aug 30 08:20
- Autumn Statement 2023 tipped to prioritise mental health, in a boost for UK workplaces Aug 29 08:33
- Final reminder for contractors to respond to the umbrella consultation (closing today) Aug 29 08:09
- Top 5 most in demand cyber security contract roles Aug 25 08:38
- Changes to the right to request flexible working are incoming, but how will contractors be affected? Aug 24 08:25
Comment