excel formula stripping text within a cell

1st. question how to dismantle an email so instead of looking at  
john.doe@email.com ,
email.JPGthe 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
ivan rosaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
For the second question, I'll split it up again with the name in A1.

B1 should be: =SEARCH(" ",A1) to find the space.
C1 should be: =LEFT(A1,B1-1) to grab the characters to the left of the space.
D1 should be: =LEN(A1)-B1 to find out how many characters there are to the right of the space
E1 should be: =RIGHT(A1,D1) to grab the characters after the space
F1 shoud be: =E1 & " " & C1 to assemble the names in the order you want

Again, this could be nested into a single formula.
ivan rosaAuthor Commented:
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)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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),"@"," "))

Open in new window

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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:

if it's john@doe.com, then use:
=PROPER( SUBSTITUTE(LEFT(A1,SEARCH(".",A1) -1),"@"," "))

Open in new window

if it's john.doe@email.com, then use:
=PROPER( SUBSTITUTE(LEFT(A1,SEARCH("@",A1) -1),"."," "))

Open in new window

ivan rosaAuthor Commented:
+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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
quick try.... for 2nd question, if Cell A4 = Flinstones, Wilma

then use this formula:

=IF( ISERROR( SEARCH(",",A4)-1 ), A4,  TRIM(RIGHT(A4, LEN(A4)- SEARCH(",",A4))) & " " & TRIM(LEFT(A4,  SEARCH(",",A4)-1)))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ivan rosaAuthor Commented:
I wasn't aware of none of those commands, Ryan is a like a walking Excel encyclopedia. tell us the truth ryan are you one of the architects for excel? :)

good job guys !
ivan rosaAuthor Commented:
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Tks Ivan, I'm just another ordinary learner... but I take it as a compliment, cheers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.