How to split a full name in 1 column into 2 columns (C1 - First name, C2 - Last name (*but here's the tricky - what if there's a middle initial?

[Mavericx]

Hi everyone!

So like the title says, I'm trying to figure out if it's possible to have a macro that can take a full name from one column and split it up into 2 columns, but IF there is a middle initial, just add it to the "first name" Column (or just deleting it would be fine as well). (example below)

I know if I didn't have to take into account a potential middle initial, a simple "text to columns" w/a Space delimiter would get the job done....but don't see how to do it that way without creating extra columns. (*I realize suffixes could definitely be an issue as well)

Any thoughts are very much appreciated!!

Full Name	First Name	Last Name
Joey Dockett	Joey	Dockett
Anne K. Franklin	Anne K.	Franklin

 

[Joe4]

This often is an exercise in futility, as people can also have multiple word first names (like "Mary Beth Smith"), and multiple word last names (like "Martin Van Buren").
This makes it virtually impossible to come with up with some sort of "rule" you can program that will always work correctly.

Typically, the best you can do is to come up with a formula that might work 95% of the time (give or take), but that would then still require you to view all results and make corrections where necessary.

 

[Sanjeev1976]

For the given sample, you can use the below :

Book2
	A	B	C
1	Full Name	First Name	Last Name
2	Joey Dockett	Joey	Dockett
3	Anne K. Franklin	Anne K.	Franklin
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(A2,LEN(A2)-LEN(C2)-1)
C2:C3	C2	=TAKE(TEXTSPLIT(A2," "),,-1)

 

[mumps]

Try:

Sub SplitName()
    Dim txt As Variant, c As Range
    For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
       txt = Split(c.Value, " ")
       If UBound(txt) = 1 Then
            c.Offset(, 1) = txt(0)
            c.Offset(, 2) = txt(1)
        Else
            c.Offset(, 1) = txt(0) & " " & txt(1)
            c.Offset(, 2) = txt(2)
       End If
    Next
End Sub

 

[Mavericx]

This often is an exercise in futility, as people can also have multiple word first names (like "Mary Beth Smith"), and multiple word last names (like "Martin Van Buren").
This makes it virtually impossible to come with up with some sort of "rule" you can program that will always work correctly.

Typically, the best you can do is to come up with a formula that might work 95% of the time (give or take), but that would then still require you to view all results and make corrections where necessary.

I hear ya Joe - as I was sitting thinking about it, I realized that it was not going to be an easy thing to tackle....if at all. Well above my (very limited) knowledge base for sure! Thx for the input.

 

[Mavericx]

For the given sample, you can use the below :

Book2
	A	B	C
1	Full Name	First Name	Last Name
2	Joey Dockett	Joey	Dockett
3	Anne K. Franklin	Anne K.	Franklin
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(A2,LEN(A2)-LEN(C2)-1)
C2:C3	C2	=TAKE(TEXTSPLIT(A2," "),,-1)

Thanks so much for taking the time to respond Sanjeev. I will definitely give this a try!

 

[Mavericx]

Try:

Sub SplitName()
    Dim txt As Variant, c As Range
    For Each c In Range("A2", Cells(Rows.Count, 1).End(xlUp))
       txt = Split(c.Value, " ")
       If UBound(txt) = 1 Then
            c.Offset(, 1) = txt(0)
            c.Offset(, 2) = txt(1)
        Else
            c.Offset(, 1) = txt(0) & " " & txt(1)
            c.Offset(, 2) = txt(2)
       End If
    Next
End Sub

Hi Mumps (love the user name btw!) - I really appreciate you taking the time to help! I will definitely give this a try as well when I get some time later. Thx again!!