repost Split fullname into First Middle and Last
 

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

http://www.microsoft.com/office/comm...f-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



--
Trish

repost Split fullname into First Middle and Last
 

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


--


Gary


"Trish Smith" wrote in message
...
Hi everyone particularly those of you who helped in my original post,

http://www.microsoft.com/office/comm...f-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



--
Trish

repost Split fullname into First Middle and Last
 

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.

--
__________________________________
HTH

Bob

"Trish Smith" wrote in message
...
Hi everyone particularly those of you who helped in my original post,

http://www.microsoft.com/office/comm...f-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



--
Trish

repost Split fullname into First Middle and Last
 

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



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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


--


Gary


"Trish Smith" wrote in message
...
Hi everyone particularly those of you who helped in my original post,

http://www.microsoft.com/office/comm...f-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



--
Trish