VBA: Extract matching first name from email address

[gmcelroy]

Hi All,

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

I also have a long list of 5k first names.

The macro needs to search each email address for a matching first name from 5k list and return the name when a match occurs. 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 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 a formula and got pretty far, but can't get it to work fully. Thought I would ask the VBA experts out there if they can come up with a solution.

Any help is greatly appreciated!!

Thanks,
Glenn

 

[oldbrewer]

[TABLE="width: 844"]
<colgroup><col><col><col span="5"><col><col><col></colgroup><tbody>[TR]
[TD]bob[/TD]
[TD]bob@gmail.com[/TD]
[TD="colspan: 2"]@@@@@[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fred[/TD]
[TD]fred@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sue[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]prudence[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]frederic[/TD]
[TD]frederic@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bob@gmail.com[/TD]
[TD]bob[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sid@gmail.com[/TD]
[TD]sid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]tom@gmail.com[/TD]
[TD]tom[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fred@gmail.com[/TD]
[TD]fred[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]wayne@gmail.com[/TD]
[TD]wayne[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fatherxmas@gmail.com[/TD]
[TD]fatherxmas[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cristopher@gmail.com[/TD]
[TD]cristopher[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]frederic@gmail.com[/TD]
[TD]frederic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=LEFT(H14,SEARCH("@",H14)-1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]@@@@@[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]=OFFSET($I$13,MATCH(A2,$I$14:$I$21,0),-1)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gmcelroy]

Hi Oldbrewer,

Thanks for your reply, but I'm not quite sure I understand your solution. Hopefully the example below further explains what I'm looking for:

Column A = List of email addresses
Column B = Extracted first name (if there is a match)
Column C = List of all 5k first names

​

[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]glenn123@gmail.com[/TD]
[TD]Glenn[/TD]
[TD]Alex[/TD]
[/TR]
[TR]
[TD]allstarcook@yahoo.com[/TD]
[TD][/TD]
[TD]Chris[/TD]
[/TR]
[TR]
[TD]steve.jones@hotmail.com[/TD]
[TD]Steve[/TD]
[TD]Dan[/TD]
[/TR]
[TR]
[TD]merrychristmas@gmail.com[/TD]
[TD][/TD]
[TD]Daniel[/TD]
[/TR]
[TR]
[TD]chris.walkin@yahoo.com[/TD]
[TD]Chris[/TD]
[TD]Danielle[/TD]
[/TR]
[TR]
[TD]daniel5593@msn.com[/TD]
[TD]Dan[/TD]
[TD]Glenn[/TD]
[/TR]
[TR]
[TD]harry_alex@gmail.com[/TD]
[TD][/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


I believe your solution is just returning the text left of the "@" symbol which won't work since the emails all have different formatting.

 

[oldbrewer]

all email addresses have @ in them

 

[gmcelroy]

all email addresses have @ in them

Of course! But the text to the left of the @ is different for all email addresses as seen in Column A of my example.

 

[oldbrewer]

agreed, but you then look for a match with any of your list of first names - harry_alex will match with harry and alex

 

[gmcelroy]

agreed, but you then look for a match with any of your list of first names - harry_alex will match with harry and alex

That's one of the problems I was running into at first. For that reason, the first criteria in my OP is as follows:

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.

In the case of harry_alex@gmail.com, only "Harry" should be returned.

Do you think this can be done using a formula? Or is VBA needed?

Thanks for your help oldbrewer!