Link to home
Start Free TrialLog in
Avatar of ivan rosa
ivan rosaFlag for United States of America

asked on

excel formula stripping text within a cell

1st. question how to dismantle an email so instead of looking at  
john.doe@email.com ,
User generated imagethe cell would strip it to the first name and last name like so,
User generated image
My next question how would I sort the names differently
Flinstones, Willma
User generated image
it would show
Willma Flinstones
User generated image
Avatar of CompProbSolv
CompProbSolv
Flag of United States of America image

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
Avatar of CompProbSolv
CompProbSolv
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ivan rosa

ASKER

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

it gives me this

User generated image
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),"@"," "))

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
+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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 !
Awesome!
Tks Ivan, I'm just another ordinary learner... but I take it as a compliment, cheers