SEARCH function for multiple variables - VLOOKUP?

Hello All, I'm a forum virgin and have been going crazy trying to solve a problem. I hope this wont be but I believe this is a tough one!

I have a large database of email addresses. I want to return the country, company name and first name and remove emails containing certain "bad" words. e.g. "unsubscribe".

Purpose to upload to my database. Most of this can be obtained using the SEARCH function. i.e. if the cell contains ".co.uk" then return "United Kingdom". I started manually adding them to formula but with nearly 200 variables this has already become unmanageable.

e.g. =IF(ISNUMBER(SEARCH("*.za*",C2)),"South Africa",IF(ISNUMBER(SEARCH("*.mu*",C2)),"Mauritius",IF(ISNUMBER(SEARCH("*.ke*",C2)),"Kenya" etc etc etc.

I can apply this to the other fields. For first name though there are potentially 1000's of variables!

So my thought is, can I combine the SEARCH and VLOOKUP functionality, so I just make a list of variables rather that putting them into a formula.

For "First Name" I started this one =IF(ISNUMBER(SEARCH($G$2,B2)),$G$2,IF(ISNUMBER(SEARCH($G$3,B2)),$G$3,IF(ISNUMBER(SEARCH($G$4,B2)),

So it does look at a list but for me to lengthen this formula to hundreds is too fiddly and time consuming.

Or is there a much easier way?!

This would save hours of manual work if I can crack it :-)

Fingers crossed!

That's fantastic.

Please explain the 17^17 part, what does 17 refer to?

I have extended the search range of the formula =LOOKUP(17^17,SEARCH("*."&Sheet2!$A$1:$A$500&"*",C4),Sheet2!$B$1:$B$500)

How do I make it return a blank if it doesn't find a relevant search field? i.e. not #N/A. normally I put ,"" at the end.

Can I apply this to First Name, company name etc?

What if I have Andrew, Andre, Andrea. I will sort them alphabetically. Will it return Andre for Andrew?

You're a star!

Countries - First Name - Entity - Company name i.e. [email protected]. I want to run If searched like with the country one. So I can pull out South Africa (.za)- Robert - Higher Education (.ac) - Shell.

I also will do something similar to remove email addresses containing given words i.e no-reply, unsubscribe.

I have a piece of software that pulls emails from my outlook. I have 60,000 email addresses that I am trying to associate all of the above where I can! So I'm using assumptions in some cases like first name.

Hope this makes sense? I could attach the spread sheet as it is?

TO SEND.xlsx

Please find attached.

Note this also contains my shockingly bad ways of doing this. I'm sure you'll laugh at columns N> !

On sheet2 I need to remove the blank spaces before the country names.

I obviously need to find a first names database. I'm worried about ones like, Jon and Jonathan. I want to be able to add new names and countries etc on an ongoing basis.

Appreciate this!

It's a mess as it is hey!!!

What I want to achieve: from the email address, tell me First Name, Company Name, Entity, Country. Also mark cells containing certain "bad" words to be removed.

So 3 phases I suppose.

1 Break the email address up into certain segments. i.e. Name (before the @), domain (PROPER after @), Country Suffix (bit after the last dot, i.e. .co.uk = ".uk"), Entity suffix, i.e. .ac, .gov after the @.
2 Create lists to LOOKUP. As already done with the counties in sheet2.
3 Formulas
Countries: nearly there just need to make it look up after and including the last dot.
First Name: need to make database and import to a list. Can return the same value as the SEARCH. Will this have alphabetical problems?
Company name: To return the bit after the @ and before the next dot. Unless it contains certain words. i.e. Gmail, Hotmail, Yahoo.
Entity: To search the bit after the @ and look for .ac, .gov etc.
Unwanted emails: If the email contains for instance "unsubscribe" in the cell. Mark it so I can bin it.

I would like to add all my lists like the country one to sheet2 so as and when I can easily add to it.

Does this make sense?!

Excited to get this working!

TO SEND take 2.xlsxHiya, I have tidied up my mess and you will see on sheet2 my idea behind the lists.

Things that do not work:

1 I can't get the entity LOOKUP formula to respond.
2 Names, please see highlighted in yellow as an example.
3 Advice on how to search for the bad words formula.
4 Company name - looks ok??

Regarding, whether its a surname or not there is no way to really tell, but I would like it to return a best guess first name for instance, as per my list.

I think I'm nearly there, thanks to you!! :D

Great.

Regarding first name can I make it stick with the first name it finds. I know occasionally this will be wrong. So andrebilly@ would return Andre.

Regarding bad words list, assuming it's now definitive as per sheet2 how do I create this? I assume in the same way as previous formulas?

How do I pull the last info from a cell so I just get the last dot plus text? I.e. Pwc.co.za returns .za ? For my countries lookup I need this!

Thanks again

Hi,

though I'm not sure how you want to deal with cases like .co.za.edu

That was a fake email, the .ac, .gov. and .edu representing the entity are in this format

[email protected] / .ac.za and just .edu (no country representation) - so I'm quite happy with the last two letters replresenting the country. in the case of .edu, I can make "du" return a blank.

First Names!

Good points! I would like it to search the string from the left finding the longest word. Will this work with [email protected] - returning Alexander?

Excited to put this all in place now! Just some fiddling to go!

Thinking ahead, with my LOOKUP lists can I make the ranges longer than needed so I can add to the bottom, i.e. if a new country gets added. Then I just have to manually sort - right?

When I paste my long list of emails into column A when this is ready do I just copy the formulas down, or is there an automated way to do it if the cell in row A is not blank? (prob just over complicating it!)

Thanks so much! Looking forward to getting this sorted!

I totally understand your point. I'm trying to make the formula a best guess at the first name. As typical surnames are not in my LOOKUP it will only rarely return an incorrect first name. i.e. James or Stewart possibly being both.

I'm happy to go with your proposal here;

We could have the algorithm go through all strings of all lengths from all starting points, if you want, i.e. as well as the above, it would also then search for:

"a", "al", "ale", "alex", ... ,"l", "le", "lex", "lexa", ..., "e", "ex", "exa", "exan", ... , "r"

That would give me the best chance of getting the correct name. I know its not 100%!

Cheers, nearly there ! :-)

Also my countries are all " South Korea"(space before the first letter). How do I remove an initial space? And not all the spaces? Normally I would do a find and replace!

TO SEND take 3.xlsx

Please see the attached, not working. Am I doing something wrong?

Regarding the Company Name, the same process as on most doesnt really work.

It needs to be =PROPER(LEFT(REPLACE(A2,1,FIND("@",A2),""),FIND(".",REPLACE(A2,1,FIND("@",A2),""))-1))

But !

if it contains one of the words in my LOOKUP Sheet2! M3-M6. Then I want it to return X, in this case a blank. To remove words Hotmail, Gmail etc.

Nearly there!

I was playing trying to get it working. My bad, apologies. Works like a dream!

I think once I sort the bit about the company name I am good to go.

I can't thank you enough. Do I acknowledge this is resolved? Do you get an improved status or anything?

Thanks so much

Exactly! Thanks

I'm currently implementing all the formulas. Thanks so much for your help.

You've been amazing, well that's a complete understatement!

I've removed pretty much all first names with less than 5 characters as it was too conflicting. My names LOOKUP list is now 3000 long!

I've had to take out a few country codes such that conflicted too.

I've been trying to find a list of spam/noreply words associated to email addresses. I just have to manually look for words at moment.

As I've pulled 60,000 emails from Outlook I want to minimise hard bounce emails!

Any extra ideas welcome

I hadn't even started!!!

Thanks so much.

So how does this forum work? Can I recommend you or something?

Cheers

1 last thing!

Countries:

=IFERROR(LOOKUP(100^100,SEARCH("*"&Sheet2!$A$2:$A$256&"*",K2),Sheet2!$B$2:$B$256),"Other")

Works great except I was a bit naive, its not just the bit after the last dot I need. I found a problem as I had to remove a few conflicting countries. i.e. Burundi. The code is .bi however I had a lot of .biz's. So removed it.

Can you tell me how to make it a rule where it looks for the last dot but only considers the last 2 digits.

Cheers.

I added reputation. Least I can do!

Sorry this is prob the bit that needs adjusting

=TRIM(RIGHT(SUBSTITUTE(A2,".",REPT(" ",255)),255))

Sorry!

Basically I only want it to return values that are 2 digits in length. So I'm assuming a LEN/COUNT somewhere?!

e.g.

.uk returns uk
.eg reyurns eg
.com returns a blank

all countries have 2 letters, so this should work?!

Cheers

That's cracking. Sorry about the thread confusion. As it was an isolated request and I had already ticked "Add Reputation" I figured that was the practice.


OK, so now my task perhaps does require a new thread!


I want to clean data where there is already some information that I want to keep.

i.e. I have an email address database with correct information but spread sporadically. i.e. First Name, Country etc my be missing. I want the values assigned to remain. But where there is no country I want it to have one.

I want to run my formulas against the blanks.

My thought process is to filter the blanks one by one by field. i.e. Country, filter blanks then manually copy my formula into the blanks. This is very labor intensive and prone to human error though!

Is there a way I can dump a database over my current formulas but it only runs the formulas on blanks. Then returning a value where applicable? Or is this way out of Excels league?

Cheers.

That'll prob work yeah.

Can I make it return the value if there was one though. I suppose I would need to change each formula accordingly?

i.e. If already a first name then put that value if not run the formula!

Nearly there now!!! You would have had this sorted in minutes I know! Trouble is with so many email addresses there are certain things that break my rules.

i.e. [email protected]

My formula currently returns the Company as za - ann.biger@"za".didata.com

So I know this is a pain but can I have a rule that if certain text"." strings after an @ then return the bit after the next .

So I want it to return didata as the Company !

current formula is;

=IF(ISNUMBER(MATCH(PROPER(LEFT(REPLACE(A4077,1,FIND("@",A4077),""),FIND(".",REPLACE(A4077,1,FIND("@",A4077),""))-1)),Sheet2!$L$2:$L$8,0)),"",PROPER(LEFT(REPLACE(A4077,1,FIND("@",A4077),""),FIND(".",REPLACE(A4077,1,FIND("@",A4077),""))-1)))

I operate in the African market and so far have only come across "za" for South Africa which is relevent to this request.

Should I have a further look up or can you put an further IF into the formula!

Cheers

Hope you had a good weekend! Sorry for the delayed reply.

Basically the main mistakes in my "Company Name" field were assigned to a handful of pre suffixes. For instance @za.pwc.com - @tz.ibm.com. There appear to be just a a few of these pre suffixes which refer to countries. So with those example I want the country to be assigned by the first two letters "za" and "tz" and the company to be the bit after the first.

So to do this, can we do a new IF within the formula, so IF equals "za", "ae", "za", "tz", "eu" or "ts". Then it applies a different rule. Perhaps I may need to edit this list but I do not envisage it getting too long. Ideally it would look these differences up in a LOOKUP. So sheet2 could have.

za - South Africa
tz - Tanzania

etc....


For the company name as it is;

=IF(ISNUMBER(MATCH(PROPER(LEFT(REPLACE(A2,1,FIND("@",A2),""),FIND(".",REPLACE(A2,1,FIND("@",A2),""))-1)),Sheet2!$L$2:$L$15,0)),"",PROPER(LEFT(REPLACE(A2,1,FIND("@",A2),""),FIND(".",REPLACE(A2,1,FIND("@",A2),""))-1)))

Would I be really over complicating it by adding a lookup for certain company names. i.e. if pwc - return Price Waterhouse Coopers, uf - return University of the Free State. etc. . . .


Cheers,

Hiya, did you have a chance to look at my last request?

Thank you