How can I strip out a name from a cell, see example below, I would need to remove the number and flip the name. Any ideas. Thanks.
example:
Column A = Column B results
Blow/Joe 123456 = Joe Blow
Johnson/Mike 898930 = Mike Johnson
Mama/Joe 293810 = Joe Mama
Smith/John 389301 = John Smith
Just use the text to columns feature. First delimit based on spaces, then delimit based on "/". Then concatenate the first and last name in the desired order.
are they all
name/name space?
or was that just your example
so everything upto the first space is the name
and then the / splits the name
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
With data in A1, in B1 enter:
=MID(A1,FIND("/",A1,1)+1,FIND(" ",A1)-FIND("/",A1)) & " " & LEFT(A1,FIND("/",A1)-1)
See the attached example
Last edited by Jakobshavn; 07-26-2014 at 07:14 AM.
Gary's Student
@ etaf, yes everything is the same but sometime it can be two first names, ie. Smith/John Michael 129321
@Jakobshaven, that formula is giving me a Value error
I love this one
Formula:
Please Login or Register to view this content.
Cell A1 is where the original data is.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
or it can be: smith jones/john michael 123421. It's really anything left of the six numbers and flip the names on each side of the "/"
The consistentancy of the data is that it's always six numbers at the end and first and last is split with a "/", but first and last can be two names.
smith jones/john michael 123421 = john michael smith jones
Last edited by Mile029; 07-25-2014 at 04:07 PM.
Tried that one. Still workssmith jones/john michael 123421
yes, Tsjallie, that works, wow, what a formula. thought it would be easier, but I'll use it. Thanks!
Yes, I feel a bit ashamedwhat a formula
Need explaination?
Also you can step thru the formula with the Evaluate Function option in the Formalas menu of the ribbon.
Substituting parts of the formula with names would make it more readable.
And so would putting it into a User Defined Function.
Last edited by Tsjallie; 07-25-2014 at 04:38 PM.
Thanks Tsjallie, totally works, but now I have a new hurdle, after going through all the records, about 50K, some don't have the numbers at the end, so gotta figure that out. Thanks.
I knew this would comeForgot to add that. Get back to you later. Solution is very simple. Will add example with names and a clarification of the construction.
I assumed you got an error on that because FIND wouldn't find anything, but that's not the case because [A1&"0123456789"] is dealing with that.some don't have the numbers at the end, so gotta figure that out
So what problem do you encounter?
Maybe as a little bit smaller formula
=TRIM(MID(SUBSTITUTE(REPLACE(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),255,""),"/",REPT(" ",255)),255,255))&" "&TRIM(LEFT(SUBSTITUTE(A1,"/",REPT(" ",255)),255))
Blow/Joe 123456 Joe Blow Johnson/Mike 898930 Mike Johnson Mama/Joe 293810 Joe Mama Smith/John 389301 John Smith smith jones/john michael 123421 john michael smith jones
Last edited by AlKey; 07-29-2014 at 07:20 AM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Or this
=IF(ISNUMBER(RIGHT(A1)+0),REPLACE(LEFT(A1,LEN(A1)-7),1,FIND("/",LEFT(A1,LEN(A1)-7)),"")&" "&LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,100)&" "&LEFT(A1,FIND("/",A1)-1))
Last edited by AlKey; 07-29-2014 at 07:24 AM.
This should work with strings as given:
Formula:Please Login or Register to view this content.
If a middle name is used then this will work:
Formula:Please Login or Register to view this content.
Last edited by newdoverman; 07-26-2014 at 03:56 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Not sure about the alternative formulas.
They all assume the number is there and the number is 6 digits long (or 7 including a space).
Can't get 'm to work on a cell when the number is missing.
@Tsjallie
See message 7.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks