HI, I'm trying to use formulas to separate email addresses in a list into three component parts: first name, last name, company name.

For example, [email protected] into "hazel", "jones" and "mycompany"


I have managed to use formulae to parse the first name and the company name, but I am having trouble getting consistent results to get the last name.


The email address is in, say, cell C2

For first name I use:
=LEFT(C2,FIND(".",C2,1)-1)

For company name I use:
=RIGHT($c2,15) (Fortunately it's the same 15 letter company name each time, I'm doing this part to check for typos)

For the last name I couldn't work out a way of using C2. Instead, I first used another formula to put the data before the "@" into a column of it's own.... =LEFT(C2,FIND("@",C2,1)-1) which gave me, for example, hazel.jones
I then tried to use the RIGHT function to on this new cell, to get the last name. The fomula I tried was =RIGHT(Y2,FIND(".",Y2,1)-1) but this gave me inconsistent results down the list of names. Sometimes I got the "." included; sometimes I got the correct last name; sometimes I got only part of the last name.

I tried changing the -1 to other values, both positive and negative. This gave me different results, but the results were not consistent in the list.

I know that I can manually parse the data, but I'd really like to do this as a formula so I don't have to parse each time I put new names in the list.

Can anyone help me please?

Thanks in anticipation