Solved

Splitting Name into FirstName, MiddleName LastName

Posted on 2011-09-15
8
386 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 27

Expert Comment

by:davorin
Comment Utility
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 167 total points
Comment Utility
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
Comment Utility
Sorry, of course I meant B2, C2 and D2 as per the attachment....

barry
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 166 total points
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Guys
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now