Solved

Splitting Name into FirstName, MiddleName LastName

Posted on 2011-09-15
8
429 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 167 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 166 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 167 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 92

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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