Extracting name from email address

Hi Nick,

I find it best to break a problem into small steps (that is how my brain works).

I have added a Header Row for (guess what?) headings 😉.

Step 1. Change the email address to plain text (Column B) so it doesn't keep opening Mail every time you click on it.

Formula in B2

=PLAINTEXT(A2)

Step 2. Find the position of the dot between firstname and lastname. Formula in C2

=FIND(".",B2,1)

Step 3. (Your solution for first name). Formula in D2

=LEFT(B2,(SEARCH(".",B2,1)−1))

Step 4. Find the position of the @. Formula in E2

=FIND("@",B2,1)

Step 5. Find lastname. Formula in F2

=MID(B2,C2+1,E2−C2−1)

You can hide the intermediate columns.

Regards,

Ian.

If you want it all in one formula, this seems to work:

=MID(A2,FIND(".",A2,1)+1,FIND("@",A2,1)−FIND(".",A2,1)−1)

This was derived by breaking the problem down first, similar to Ian's approach, and then after each piece was working as expected, I combined the elements into one formula.

SG

Hi SG,

Nice! Your reply made me look again at FIND and SEARCH.

From the Function Browser:

The FIND function returns the starting position of one string within another.

The SEARCH function returns the starting position of one string value within another, ignoring case and allowing wildcards.

Either function seems to work in this case, but I found it useful to learn about case and wildcards.

Regards,

Ian.

Hi Ian,

Great reminder on the difference between FIND and SEARCH. I have alway been a little unsure about that (one of those "wildcards of the mind", if you know what I mean.🙂).

SG