repost Split fullname into First Middle and Last

[Trish Smith]

Hi everyone particularly those of you who helped in my original post,

http://www.microsoft.com/office/com...&p=1&tid=4777b66d-48fd-478c-a28f-f514966f3897

Here's what I asked

I'm very new to this and thought that as a challenge to myself I would try
to set up code to split FullName in Column A to First, Middle and Surname in
B,C and D.

I'd normally do this using formulas that I copy down the range from Peter
Noneley's xlfdic02
Firstname - =LEFT(A1,FIND(" ",A1,1)) in B1

Middlename - =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND("
",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1)) in C1

Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"
","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) in D1

Thanks to all of you who answered especially the additional questions about
how the code worked.

I realised too late that although the code was splitting the string
beautifully (and so fast) that if the string had just firstname and surname
then the surname was being placed in column C and C was then a mixture of
Surname and Middle name.

If anyone could help me any further with this I would be over the moon

Thank you

 

[Gary Keramidas]

posted this to your original, too

Sub test()
Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet
Dim arynames As Variant
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
With ws.Range("A" & i)
arynames = Split(.Value)
.Offset(, 1).Value = arynames(0)
If UBound(arynames) = 1 Then
.Offset(, 3).Value = arynames(1)
Else
.Offset(, 2).Value = arynames(1)
.Offset(, 3).Value = arynames(2)
End If
End With
On Error GoTo 0
Next
End Sub

 

[Bob Phillips]

I haven't followed the other thread, but is this what you mean?

Function SplitMyName(pName As Variant)
Dim aryNames As Variant

aryNames = Split(pName, " ")
If UBound(aryNames) = LBound(aryNames) Then

ReDim Preserve aryNames(0 To 2)
ElseIf UBound(aryNames) = LBound(aryNames) + 1 Then

ReDim Preserve aryNames(0 To 2)
aryNames(2) = aryNames(1)
aryNames(1) = ""
End If
SplitMyName = aryNames

End Function

You would select cells B11, enter the formula

=SplitMyName(A1)

and array-enter.

 

[Rick Rothstein \(MVP - VB\)]

Here is a slight modification (posted you original too) to your posted code
which you may wish to consider...

Sub test()
Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet
Dim arynames As Variant
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
With ws.Range("A" & i)
arynames = Split(.Value)
.Offset(, 1).Value = arynames(0)
.Offset(, 3).Value = arynames(UBound(arynames))
If UBound(arynames) = 2 Then
.Offset(, 2).Value = arynames(1)
End If
End With
Next
End Sub

Rick