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]

bob	bob@gmail.com	@@@@@
fred	fred@gmail.com
sue	#N/A
prudence	#N/A
frederic	frederic@gmail.com
								#####
							bob@gmail.com	bob
							sid@gmail.com	sid
							tom@gmail.com	tom
							fred@gmail.com	fred
							wayne@gmail.com	wayne
							fatherxmas@gmail.com	fatherxmas
							cristopher@gmail.com	cristopher
							frederic@gmail.com	frederic
			#####
			=LEFT(H14,SEARCH("@",H14)-1)
			@@@@@
			=OFFSET($I$13,MATCH(A2,$I$14:$I$21,0),-1)

<colgroup><col><col><col span="5"><col><col><col></colgroup><tbody>
</tbody>

 

[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

​

A	B	C
glenn123@gmail.com	Glenn	Alex
allstarcook@yahoo.com		Chris
steve.jones@hotmail.com	Steve	Dan
merrychristmas@gmail.com		Daniel
chris.walkin@yahoo.com	Chris	Danielle
daniel5593@msn.com	Dan	Glenn
harry_alex@gmail.com		Mark
		Steve
		...

<tbody>
</tbody>

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!