?
Solved

Splitting Name into FirstName, MiddleName LastName

Posted on 2011-09-15
8
Medium Priority
?
431 Views
Last Modified: 2012-05-12
I have a spreadsheet that has a column called “Patient Name”. This column has names of people in “LastName”,”FirstName” “Middle Name” format. Some have middle names and some don’t because it is optional.  I want to split this column into 3 columns “FirstName” “MiddleName” “LastName”. How can I accomplish this goal. Attached is a copy of the Excel spreadsheet with the 3 columns that are currently blank
Names.xlsx
0
Comment
Question by:Chrisjack001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 27

Expert Comment

by:davorin
ID: 36544815
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 668 total points
ID: 36544822
Hello Chrisjack001,

try these formulas in A2, B2 and C2 respectively

=MID(LEFT(A2,FIND(" ",A2&" ")-1),FIND(",",A2)+1,99)

=MID(A2,LEN(B2&D2)+3,99)

and

=LEFT(A2,FIND(",",A2)-1)

see attached

regards, barry
27310507.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36544829
Sorry, of course I meant B2, C2 and D2 as per the attachment....

barry
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 664 total points
ID: 36544889
Insert the following formulas in cells B2, C2, and D2, respectively and then copy down:

B2
=IF(ISERR(FIND(" ",A2)),MID(A2,FIND(",",A2)+1,999),LEFT(MID(A2,FIND(",",A2)+1,999),FIND(" ",MID(A2,FIND(",",A2)+1,999))-1))

C2
=IF(ISERR(FIND(" ",A2)),"",MID(A2,FIND(" ",A2)+1,999))

D2
=LEFT(A2,FIND(",",A2)-1)

This only works if there are no compound names like "SUE ANN" where a space separates two parts of a name.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36544932
Also, none of the above formulas from me or barryhoudini will catch names like
BUELL,FRANKLIN T III

where there is a name suffix like "JR" or "III"  (i.e., FRANKLIN T BUELL III)
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 668 total points
ID: 36545008
Name parsing is something that sounds so easy, but can actualy get complex in a hurry.  I wrote an article on the subject here:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1819-Parsing-Names-in-MS-Office-Visual-Basic-6-and-Visual-Basic-for-Applications.html

In my experience, you can rarely count on such "combined" names to be formatted consistently, and so I came up with a VBA aproach that, like any, has its limits, but is a little more fault tolerant.

1) Add the code from that article to a regular VBA module

2) Use formulae like this to get the name parts:

=GetNamePart(A2,"fname",TRUE,FALSE)
=GetNamePart(A2,"mname",TRUE,FALSE)
=GetNamePart(A2,"lname",TRUE,FALSE)

It will work no matter whether a middle name is supplied, but compound names can be problematic.  It will also--up to a point--handle common titles/suffixes.

Using GlennLRay's example in http:#a36544932, it will return invalid output for "BUELL,FRANKLIN T III", but it will work just fine for "BUELL III,FRANKLIN T III".

Also, my approach will not care if an entry is

ALEXANDER,DEBORAH ANN

or

ALEXANDER, DEBORAH ANN
0
 

Author Closing Comment

by:Chrisjack001
ID: 36550734
Thanks Guys
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36550806
Chrisjack001,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1819-Parsing-Names-in-MS-Office-Visual-Basic-6-and-Visual-Basic-for-Applications.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question