Extracting first name from dissimilar email addresses

[gmcelroy]

Hi All,

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

I'm able to extract the first name where possible using a list of 5,000 first names as my search values and the email address as my text to search within. I'm also only searching text that is left of the @ symbol. Here's the formula I'm using:

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

Where 'first names'!$A$2:$A$5313 is the list of first names and 'A2' is the email address. This works great, but I now want to only return a value if the email address STARTS with the first name. For example, I want chris@gmail.com to return "Chris" but merrychristmas@gmail.com to return null.

Column A	Column B
chris@gmail.com	Chris
merrychristmas@gmail.com	NULL

<tbody>
</tbody>

Any help is greatly appreciated!!

Thanks,
Glenn

 

[RonB1111]

Perhaps,

=IFERROR(VLOOKUP(LEFT(A2,FIND("@",A2)-1),'First Names'!A$2:A$5313,1,FALSE),"")

 

[gmcelroy]

Hi Ron, thanks for the reply!

I tried your formula and it works well for emails that only have a first name before the @ symbol. However, it doesn't work for emails that have more than that. For example:

chris@gmail.com <--Returns 'chris'
chris.jones@gmail.com <-- Returns nothing

Any ideas on how to fix this?

Thanks!
Glenn

 

[RonB1111]

Are there any other formats besides chris@... or chris.jones@...
For example, could it ever be chrisjones@... or jones.chris@... or anything else?

 

[gmcelroy]

Are there any other formats besides chris@... or chris.jones@...
For example, could it ever be chrisjones@... or jones.chris@... or anything else?

Yes, it could be chris@, chris.jones@, or chrisjones@. Basically any address that starts with a matching first name. I guess you could say the format is <first_name>XXXX@domian.com, where X is any number of wildcard characters.

Thanks
glenn

 

[RonB1111]

This will handle the chris@ and chris.jones@ format, but not for chrisjones@ because I don't have a way to determine where firstname ends and lastname begins.

=IFERROR(VLOOKUP(LEFT(A2,FIND("@",A2)-1),'First Names'!A$2:A$5313,1,FALSE),IFERROR(VLOOKUP(LEFT(A2,FIND(".",A2)-1),'First Names'!A$2:A$5313,1,FALSE),""))

 

[gmcelroy]

This will handle the chris@ and chris.jones@ format, but not for chrisjones@ because I don't have a way to determine where firstname ends and lastname begins.

=IFERROR(VLOOKUP(LEFT(A2,FIND("@",A2)-1),'First Names'!A$2:A$5313,1,FALSE),IFERROR(VLOOKUP(LEFT(A2,FIND(".",A2)-1),'First Names'!A$2:A$5313,1,FALSE),""))

Thanks for your help RonB! Unfortunately I really need it to work for all cases...otherwise I'll be leaving a lot of emails on the table.

Is there a way to edit my original formula so that it only returns a value if the email STARTS with the first name?

[COLOR=#333333]=IFERROR(LOOKUP(1E+100,SEARCH('first names'!$A$2:$A$5313,LEFT(A2,FIND("@",A2)-1)),'first names'!$A$2:$A$5313),"")[/COLOR]

 

[RonB1111]

Is there a way to edit my original formula so that it only returns a value if the email STARTS with the first name?

You may need to clarify your question. This formula will return results in the following order:
1. Entire string on the left of @ if it matches a name in your First Names list
2. Entire string on the left of the first "." if it matches a name in your First Names list
3. Entire string on the left of the @ (if there was no match in the 2 conditions above).

=IFERROR(VLOOKUP(LEFT(A2,FIND("@",A2)-1),'First   Names'!A$2:A$5313,1,FALSE),IFERROR(VLOOKUP(LEFT(A2,FIND(".",A2)-1),'First  Names'!A$2:A$5313,1,FALSE),LEFT(A2,FIND("@",A2)-1)))

I don't know how to tell if an email STARTS with the first name. The reasons include:
1. Some names can be both 1st and last, eg: Allen, George, Arnold, Michael, Paul etc.
2. If you look at the 1st few letters to test for a match, you could easily get the name wrong. Eg: daniellegrand@xyz.com - is this Daniel LeGrand or Danielle Grand? If it's based on the 1st 3 letters "Dan", and the name is actually Danielle, she might view anything sent to her as "Dan" or "Daniel" in a negative light.
If the above formula is not useful, please provide some guidelines on how you'd identify the 1st name. While there may not be a formula solution, perhaps another contributor could come up with a VBA solution.

 

[gmcelroy]

If the above formula is not useful, please provide some guidelines on how you'd identify the 1st name.

Hi RonB,

There are 2 criteria that need to be met for the formula to return a first name:
1. The email address starts with the first name.
2. In cases where there may be multiple matches (eg. Dan, Daniel, Danielle), the formula should return the longest matching first name.

I think this should do it. We'll be sending emails out in smaller batches, so I'll be able to review the list and correct any corner cases (eg. daniellegrand).

Thanks!
Glenn

 

[RonB1111]

Hopefully another contributor will see this and have a solution (probably VBA) if its possible to do this. I'm unable to come up with one. If nobody responds then start a new thread with VBA in the title and an explanation of all the criteria.