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 [email protected]).

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 "[email protected]" should return "Chris" but "[email protected]" shouldn't return anything.

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

The email "[email protected]" 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...

Please Login or Register  to view this content.

Originally Posted by jhren

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

Please Login or Register  to view this content.

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:


[email protected] Returns "Madonna"
[email protected] 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

What do you expect for 'chrisjones@"?

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 [email protected], and frankly can be any format imaginable)

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

Please Login or Register  to view this content.

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

Any ideas?

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.

Originally Posted by István Hirsch

What do you expect for 'chrisjones@"?

Hi Istvan,

The email "[email protected]" should return "Chris"

Thanks,
G

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.

Please Login or Register  to view this content.

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