# 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
LVL 3
###### Who is Participating?

x
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.

Commented:
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.
Commented:
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.
Author 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)
Business 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),"@"," "))
``````
Commented:
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.
Business Systems Analyst , ex-Senior Application EngineerCommented:
ok..

if it's john@doe.com, then use:
``````=PROPER( SUBSTITUTE(LEFT(A1,SEARCH(".",A1) -1),"@"," "))
``````
if it's john.doe@email.com, then use:
``````=PROPER( SUBSTITUTE(LEFT(A1,SEARCH("@",A1) -1),"."," "))
``````
Author Commented:
+Ryan, I'm curious how would you solve the second question?

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

Experts Exchange Solution brought to you by

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

Author 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 !
Author Commented:
Awesome!
Business 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.