• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

Splitting Name into FirstName, MiddleName LastName

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
Chrisjack001
Asked:
Chrisjack001
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
barry houdiniCommented:
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
 
barry houdiniCommented:
Sorry, of course I meant B2, C2 and D2 as per the attachment....

barry
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Glenn RayExcel VBA DeveloperCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
Patrick MatthewsCommented:
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
 
Chrisjack001Author Commented:
Thanks Guys
0
 
Patrick MatthewsCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now