Extract matching first name from unformatted email addresses

Hello,

I have a long list of email addresses, all in random formats (they are not your standard first.last@domain.com).

I also have a list of 5k first names.

The formula/macro needs to search each email address for a matching first name from 5k list and return the matching name. The logic is pretty straightforward:

1. Return a value if the email address STARTS with one of the names in the list of 5k names. For example:

The email "chris@gmail.com" should return "Chris" but "merrychristmas@gmail.com" shouldn't return anything.

2. In cases where more than one match exists, return the longest match. For example:

The email "danielle@gmail.com" would technically match with "dan", "daniel", and "danielle". In these cases, "Danielle" should be returned since it is the longest match.

3. If there is no match, the macro shouldn't return anything.

I tried accomplishing this using an Excel formula and got pretty far, but can't get it to search from the beginning of the email address. I upoaded my spreadsheet here:

https://drive.google.com/file/d/0B8J...ew?usp=sharing

Any ideas on how to fix my formula? I'm also open to a macro if you think that's a better solution.

Your help is greatly appreciated!!

Thanks,
Glenn

Try replacing your formula (B2) with this one and copy down...

=IFERROR(IF(LEFT(LOOKUP(1E+100,SEARCH('list of first names'!$A$2:$A$5313,LEFT(A2,FIND("@",A2)-1)),'list of first names'!$A$2:$A$5313),1)<>LEFT(A2,1),"",LOOKUP(1E+100,SEARCH('list of first names'!$A$2:$A$5313,LEFT(A2,FIND("@",A2)-1)),'list of first names'!$A$2:$A$5313)),"")

Originally Posted by jhren

Try replacing your formula (B2) with this one and copy down...

=IFERROR(IF(LEFT(LOOKUP(1E+100,SEARCH('list of first names'!$A$2:$A$5313,LEFT(A2,FIND("@",A2)-1)),'list of first names'!$A$2:$A$5313),1)<>LEFT(A2,1),"",LOOKUP(1E+100,SEARCH('list of first names'!$A$2:$A$5313,LEFT(A2,FIND("@",A2)-1)),'list of first names'!$A$2:$A$5313)),"")

Thanks jhren, that seemed to do the trick! However, I noticed some inconsistencies with some of the email addresses. Here are two examples where the formula returns a name when it shouldn't:


mmadonna4054@gmail.com Returns "Madonna"
jamesprivate@gmail.com Returns "Riva"

I've updated the spreadsheet with your formula and both of these corner cases (see attached).
Email List Elance Project.xlsx

Any ideas on how to fix this?

Thanks,
Glenn

Cross post:

http://www.mrexcel.com/forum/excel-q...addresses.html

Originally Posted by benishiryo

hi Glenn. a different approach:
=IFERROR(VLOOKUP(REPLACE(LEFT(A2,FIND("@",A2)-1),FIND(".",A2),100,""),'list of first names'!A:A,1,0),"")

Thanks for the reply benishiryo. Unfortunately this won't work because my email addresses are all in different formats (the majority of them are not first.last@domain.com, and frankly can be any format imaginable)

So far, jhren's solution has come the closest to solving my problem:

=IFERROR(IF(LEFT(LOOKUP(1E+100,SEARCH('list of first names'!$A$2:$A$5313,LEFT(A2,FIND("@",A2)-1)),'list of first names'!$A$2:$A$5313),1)<>LEFT(A2,1),"",LOOKUP(1E+100,SEARCH('list of first names'!$A$2:$A$5313,LEFT(A2,FIND("@",A2)-1)),'list of first names'!$A$2:$A$5313)),"")

However, there are some cases where it doesn't work and I can't figure out why.

Any ideas?

Thanks!
G

What do you expect for 'chrisjones@"?

Originally Posted by István Hirsch

What do you expect for 'chrisjones@"?

Hi Istvan,

The email "chrisjones@gmail.com" should return "Chris"

Thanks,
G

Originally Posted by AlKey

Somewhat similar to what benishiryo has offered but a little bit shorter

=IFERROR(VLOOKUP(LEFT(A2,FIND("@",SUBSTITUTE(A2,".","@"))-1),'list of first names'!A:A,1,),"")

Hi Alkey - Unfortunately this won't work. See my reply to benishiryo.

See if this works for you. Made list into a Table and sorted. Also made it all lower case. Don't know if the lower case made any difference but that's where it ended up.

{=PROPER(LEFT(A2,LARGE(IF(LEFT(A2,LEN(List[firstname]))=List[firstname],LEN(List[firstname]),0),1)))}

Note it is an array formula requiring Ctrl+Shift+Enter confirmation (as opposed to typical Tab, Enter, or click elsewhere)