Separating First Name(s) and Last Name into Separate Columns

[Shane R. Pouch]

I have a spreadsheet with one column ("name") that contains names in the
following formats occuring randomly down the list:

Firstname Lastname
Firstname & Firstname Lastname
Firstname Lastname & Firstname Lastname

I need to get them all into two new columns "firstname" and "lastname" and
I'm having difficulties writing a formula to account for all 3 scenarios in
one cell. (If I have to do it in multiple steps - so be it!)

For the 1st example, the contents of the new "firstname" column should be
Firstname, while the "lastname" column should be Lastname.
For the 2nd example, the contents of the new "firstname" column should be
Firstname & Firstname, while the "lastname" column should be Lastname.
For the 3rd example, the contents of the new "firstname" column should be
Firstname & Firstname, while the "lastname" column should be Lastname &
Lastname.

Examples:
Joe Blow
Joe & Julie Blow
Joe Blow & Jane Schmo

"firstname" column contents should be
Joe
Joe & Julie
Joe & Jane

"lastname" column contents should be
Blow
Blow
Blow & Schmo

Thanks for helping!

 

[Ron Rosenfeld]

I have a spreadsheet with one column ("name") that contains names in the
following formats occuring randomly down the list:

Firstname Lastname
Firstname & Firstname Lastname
Firstname Lastname & Firstname Lastname

I need to get them all into two new columns "firstname" and "lastname" and
I'm having difficulties writing a formula to account for all 3 scenarios in
one cell. (If I have to do it in multiple steps - so be it!)

For the 1st example, the contents of the new "firstname" column should be
Firstname, while the "lastname" column should be Lastname.
For the 2nd example, the contents of the new "firstname" column should be
Firstname & Firstname, while the "lastname" column should be Lastname.
For the 3rd example, the contents of the new "firstname" column should be
Firstname & Firstname, while the "lastname" column should be Lastname &
Lastname.

Examples:
Joe Blow
Joe & Julie Blow
Joe Blow & Jane Schmo

"firstname" column contents should be
Joe
Joe & Julie
Joe & Jane

"lastname" column contents should be
Blow
Blow
Blow & Schmo

Thanks for helping!

Given your rather specific data layouts, the following VBA Sub will parse the
names as you describe.

To enter the sub, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

The code operates on any range you have selected, and puts the FirstName and
LastNames into the adjacent columns, clearing those columns first.

Note that if your names do not precisely match one of the patterns you have
provided, nothing will happen.

==============================================
Option Explicit
Sub PrsNm()
Dim c As Range
Dim aFn() As String, aLn() As String
Dim re As Object, mc As Object, m As Object
Dim i As Long, j As Long, k As Long
Set re = CreateObject("vbscript.regexp")
re.Pattern = "^(\w+)\s+(\w+)$|^(\w+)\s+&\s+" & _
"(\w+)\s+(\w+)$|^(\w+)\s+(\w+)\s+&\s+(\w+)\s+(\w+)$"

For Each c In Selection
j = 0: k = 0
ReDim aFn(j): ReDim aLn(k)
Range(c.Offset(0, 1), c.Offset(0, 2)).ClearContents
If re.test(c.Value) Then
Set mc = re.Execute(c.Value)
For i = 1 To 9
Select Case i
Case Is = 1, 3, 4, 6, 8
If Not IsEmpty(mc(0).submatches(i - 1)) Then
ReDim Preserve aFn(j)
aFn(j) = mc(0).submatches(i - 1)
j = j + 1
End If
Case Is = 2, 5, 7, 9
If Not IsEmpty(mc(0).submatches(i - 1)) Then
ReDim Preserve aLn(k)
aLn(k) = mc(0).submatches(i - 1)
k = k + 1
End If
End Select
Next i
End If
c.Offset(0, 1).Value = Join(aFn, " & ")
c.Offset(0, 2).Value = Join(aLn, " & ")
Next c
End Sub
===================================
--ron

 

[Shane R. Pouch]

Ron,
Thanks! Works great! Now I just have to figure out HOW it works.

 

[Ron Rosenfeld]

Ron,
Thanks! Works great! Now I just have to figure out HOW it works.

You're welcome. Glad to help.

It uses Regular Expressions to parse the segments. (The parsing could be done
using plain VBA functions, but for me it is simpler, and faster, to use
regexes).

Here are some online references:

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/
--ron