Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split fullname into First Middle and Last | Excel Programming | |||
Citrix and FullName | Excel Programming | |||
Split one cell in the middle and fill each with one color | Excel Worksheet Functions | |||
ThisWorkbook.FullName | Excel Programming | |||
Split fullname into Drive, Path and Filename | Excel Programming |