Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Splitting Name into FirstName, MiddleName LastName

Posted on 2011-09-15
8
Medium Priority
?
434 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

618 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