repost Split fullname into First Middle and Last

  • Thread starter Thread starter Trish Smith
  • Start date Start date
T

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
 
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
 
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 B1:D1, enter the formula

=SplitMyName(A1)

and array-enter.
 
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
 
Back
Top