# excel formula stripping text within a cell

1st. question how to dismantle an email so instead of looking at
john.doe@email.com ,
the cell would strip it to the first name and last name like so,

My next question how would I sort the names differently
Flinstones, Willma

it would show
Willma Flinstones
CompProbSolv

For the first question, I'll do it in three steps to make it clearer.
If A1 has the email address, B1 should be: =SEARCH("@",A1) to find where the "@" is.
C1 should be: =LEFT(A1,B1-1) to grab the characters before the "@".
D1 should be: =SUBSTITUTE(C1,"."," ") to replace the "." with a space.

These could be nested into a single formula easily enough.  I thought that would make it more difficult to follow here.

Keep in mind that there is no error checking going on.
SOLUTION
CompProbSolv

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

nice going, although on pratice i got instead of giving out the result in one cell like
john doe

it gives me this

for the second, you got it!, I did this minor adjustment to get rid of the comma " , "
F1 =SEARCH(",",A1)
for your first question, you can format the name to Proper Case as well using Proper function, like:
=PROPER( SUBSTITUTE(LEFT(A1,SEARCH(".",A1) -1),"@"," "))
As far as the first one goes, look at what you have in D1.  It is john@doe.com and not john.doe@email.com.
SOLUTION

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

+Ryan, I'm curious how would you solve the second question?

you guys are excellent on this
On the second one, you are correct.  I tested it without the comma.

If you are certain that you will always have a comma and a space between the two names, just change C1 to: =LEFT(A1,B1-2)
Ryan's solution with Proper is a MUCH better one.  I wasn't aware of that function.

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.