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

 

[azumi]

Maybe more clear if you gave more examples from your case and your expected results...

regards

 

[gmcelroy]

Maybe more clear if you gave more examples from your case and your expected results...

I uploaded my spreadsheet to google docs which should shed more light on the problem:

https://drive.google.com/file/d/0B8Ju-m_SYEyZbFkwSVRUTm9HX0E/view?usp=sharing

I included a bunch of examples and the expected results. The formula I have now does everything except return the first name, only when the email STARTS with the first name. Right now is returns the first name regardless of whether its at the beginning or the end of the email account name.

That's really what I need help with.

Thanks,
Glenn

 

[István Hirsch]

After performing the necessary changes, enter this formula in B2 with Ctrl - Shift - Enter and copy down (now your ’first names’ is substituted with Sheet2):

To avoid finding Dan when Daniel should be found, in your „first names” sheet enter in B2=len(a2), copy down, then sort column A and B according to B from largest to smallest. So the longer names come first in the list.

=IFERROR(INDEX(Sheet2!A$2:A6,MATCH(TRUE,ISNUMBER(SEARCH("ß"&Sheet2!A$2:A$6&"*","ß"&A2)),0)),"")