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,
email2.JPG
My next question how would I sort the names differently
Flinstones, Willma
name.JPG
it would show
Willma Flinstones
name2.JPG
LVL 3
ivan rosaAsked:
Who is Participating?
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.

CompProbSolvCommented:
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.
0
CompProbSolvCommented:
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.
0
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

email.JPG
for the second, you got it!, I did this minor adjustment to get rid of the comma " , " 
F1 =SEARCH(",",A1)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
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

0
CompProbSolvCommented:
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.
0
Ryan ChongCommented:
ok..

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

0
ivan rosaAuthor Commented:
+Ryan, I'm curious how would you solve the second question?

you guys are excellent on this
0
CompProbSolvCommented:
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)
0
CompProbSolvCommented:
Ryan's solution with Proper is a MUCH better one.  I wasn't aware of that function.
0
Ryan ChongCommented:
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

0

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 !
0
ivan rosaAuthor Commented:
Awesome!
0
Ryan ChongCommented:
Tks Ivan, I'm just another ordinary learner... but I take it as a compliment, cheers
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.