Extract Last Name from Email Address

[ScratchHead_Keith]

There is a similar thread to this that was not answered.

I would like to extract just the last name from an email address. My email naming format is First.Middle.last@email, or First.Last@email. Basically, I want the name between the @ and the ".". I found this formula online "=LEFT(RIGHT(B2,LEN(B2)-FIND(".",B2)),FIND("@",RIGHT(B2,LEN(B2)-FIND(".",B2)))-1)", but it brings back the middle name too. I've tried customizing it but all my hair fell out and now I'm going bald.

 

[lrobbo314]

How about this?

Book1.xlsx
	A	B
1	First.Middle.last@email	last
2	John.Bill.Doe@test.com	Doe
Sheet3

Cell Formulas
Range	Formula
B1:B2	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1),".",REPT(" ",100)),100))

 

[ScratchHead_Keith]

Wow, that works like a charm. I have no idea how, but thanks all the same.

 

[FlameRetired]

How about this?

Book1.xlsx
	A	B
1	First.Middle.last@email	last
2	John.Bill.Doe@test.com	Doe
Sheet3

Cell Formulas
Range	Formula
B1:B2	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1),".",REPT(" ",100)),100))

Good one!