How do you split last names and two-part first names from one cell?
Our office has a list of some thousands of names written like this..
Smith, Rebbecca Carter
Doe, Jane Smith
Bacon, Dan Parker
Crawford, Sara Lopez
that I have to separate into cells for Last Name | First Name | Middle Name
my problem is when it is a two-part first name.
Fisher, Mary Anne Castro
What is a universal formula that can be used for all these names and get the correct split data?
Is the last name always separated from the rest with a "," ?
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Please let us know what is the expected output of the above example
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Yes, dredwolf. The Last name is ALWAYS separated by a comma ",".
Originally Posted by dredwolf
A2: Fisher, Mary Anne Castro
A3: Fisher
A4: Mary Anne
A5: Castro
Hi
Im not sure you can, hopefully Im wrong and someone will correct me the only way I know to do it is text to columns and then manually adjust the two parters.
Chris
Click my star if I helped Thanks
i think that's better Chris than using formulas
with regards to formulas try this two first if you have problem with the last one revert to text-to-column
=TRIM(LEFT(A2,FIND(",",A2)-1))
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
It is do-able, it's just going to take me some time to come up the formulas...string manipulation is NOT my strong suit...
Hopefully, someone a little better will come up with a solution before I finally muddle my way through it !
In A2 cell
Fisher, Mary Anne Castro
In A3 cell
Formula:
Please Login or Register to view this content.
Drag it down...
it depends on the names...
eg.
Fisher, Mary Anne De Castro
Fisher, Mary Anne Joy Castro
Fisher, Mary De Castro
Hi,
Find 3 formulas that do what you ask in the attached.
One test is worth a thousand opinions.
How about the two-part first names though?
Check my answer.
a3
=TRIM(LEFT(A2,FIND(",",A2)-1))
a4
=IFERROR(SUBSTITUTE(LEFT(SUBSTITUTE(TRIM(MID(A2,FIND(",",A2)+1,999))," ","*",1),FIND(" ",SUBSTITUTE(TRIM(MID(A2,FIND(",",A2)+1,999))," ","*",1))),"*"," "),TRIM(MID(A2,FIND(",",A2)+1,999)))
handles hyphenated first names as well
still working on the a5 solution
Edit-
I knew SOMEONE would come up with something before me...*sigh*...lol
Last edited by dredwolf; 12-06-2012 at 03:44 AM.
anyways, for what its worth
=Trim(SUBSTITUTE(TRIM(SUBSTITUTE(A2,A3&","," ")),A4," "))
Edit, the first trim may be unnecessary, but I included it just in case..I truly dislike leading spaces messing up my formulas, especially with my string 'handicap' anyways
Last edited by dredwolf; 12-06-2012 at 04:14 AM.
Yey! Thanks Everyone... This has been a pain half my day!
I knew someone can solve it!
You are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks