parsing email address into three separare fields using formulas

[dannahaz]

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

[martindwilson]

not all email addresses are in that format
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

for example
how would you deal with them?

[dannahaz]

not all email addresses are in that format
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

for example
how would you deal with them?

There company does not use _ in their email addresses. I might in theory have some instances of the [email protected] issues. I guess the ideal solution would be to work backwards from the "@" sign to the ".". But I don't know how to do that.

[martindwilson]

lets assume you can only have these 2 formats

[email protected]
[email protected]
with address in a1 then
b1

Please Login or Register  to view this content.

c1

Please Login or Register  to view this content.

d1

Please Login or Register  to view this content.

[dannahaz]

Thank you, that works perfectly

[dannahaz]

Dup Post, sorry

[martindwilson]

not in mine it doesnt

[Marcol]

Ifyou want to split at every dot and @ try this, drag across until blanks are returned

Please Login or Register  to view this content.