Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Hi everyone,
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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Hi there,
I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
if there is a space separating, then names, try this:
Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
From an efficiency view, better to do the split just once per line
Public 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(, 2).Value = aryNames(1) .Offset(, 3).Value = aryNames(2) End With Next End Sub -- __________________________________ HTH Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
This way is, perhaps, even a little bit more efficient...
Public Sub Test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Resize(1, 3).Value = Split(.Value) End With Next End Sub Rick "Bob Phillips" wrote in message ... From an efficiency view, better to do the split just once per line Public 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(, 2).Value = aryNames(1) .Offset(, 3).Value = aryNames(2) End With Next End Sub -- __________________________________ HTH Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
We can compact the code down even further (and save some variables, a Set
and an assignment)... Public Sub Test() Dim C As Range With Worksheets("Sheet1") For Each C In .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row) C.Offset(, 1).Resize(1, 3).Value = Split(C.Value) Next End With End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... This way is, perhaps, even a little bit more efficient... Public Sub Test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Resize(1, 3).Value = Split(.Value) End With Next End Sub Rick "Bob Phillips" wrote in message ... From an efficiency view, better to do the split just once per line Public 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(, 2).Value = aryNames(1) .Offset(, 3).Value = aryNames(2) End With Next End Sub -- __________________________________ HTH Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Hi Rick,
As to the other guys who have helped here - thank you so much. I've saved all the code to a module to see the different approaches (appended with lots of lines in green :-) whilst I still have an inkling of what the code actually does. It looks as though resize changes the size of the active range but how are you specifying where each of the split values is put? I'm only a beginner so if you could take it slowly that would be great. Thank you :-) -- Trish "Rick Rothstein (MVP - VB)" wrote: This way is, perhaps, even a little bit more efficient... Public Sub Test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Resize(1, 3).Value = Split(.Value) End With Next End Sub Rick "Bob Phillips" wrote in message ... From an efficiency view, better to do the split just once per line Public 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(, 2).Value = aryNames(1) .Offset(, 3).Value = aryNames(2) End With Next End Sub -- __________________________________ HTH Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Hi Bob,
Thanks for your code I'm really grateful and it helps more than you know seeing an experts approach :-) I'm not trying to be funny (genuinely want to know answer) how does splitting once per line help? Thank you -- Trish "Bob Phillips" wrote: From an efficiency view, better to do the split just once per line Public 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(, 2).Value = aryNames(1) .Offset(, 3).Value = aryNames(2) End With Next End Sub -- __________________________________ HTH Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
It just means that you only issue one call to the Split function. Doing
something once is usually better than doing the same thing three times. The statement ..Offset(, 1).Value = Split(.Value)(0) breaks the value into its constituent parts, loads them into an array, and then extracts item 0. The following statement .Offset(, 2).Value = Split(.Value)(1) breaks the value into its constituent parts, loads them into an array, and then extracts item 1. So the breaking into constituent parts is repeated. By doing the split just once and saving in an array variable means that you are saving processing. And this processing is saved for every line in your dataset, hence more efficient. -- __________________________________ HTH Bob "Trish Smith" wrote in message ... Hi Bob, Thanks for your code I'm really grateful and it helps more than you know seeing an experts approach :-) I'm not trying to be funny (genuinely want to know answer) how does splitting once per line help? Thank you -- Trish "Bob Phillips" wrote: From an efficiency view, better to do the split just once per line Public 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(, 2).Value = aryNames(1) .Offset(, 3).Value = aryNames(2) End With Next End Sub -- __________________________________ HTH Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Executing functions takes more time than referencing an array element.
Execution-wise, the Split function is not a particularly fast function; so, eliminating two Split function calls and trading them for a single array assignment and then reading three array elements from it is more efficient overall. Rick "Trish Smith" wrote in message ... Hi Bob, Thanks for your code I'm really grateful and it helps more than you know seeing an experts approach :-) I'm not trying to be funny (genuinely want to know answer) how does splitting once per line help? Thank you -- Trish "Bob Phillips" wrote: From an efficiency view, better to do the split just once per line Public 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(, 2).Value = aryNames(1) .Offset(, 3).Value = aryNames(2) End With Next End Sub -- __________________________________ HTH Bob "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Thank you Gary,
Could you explain the syntax for the Split funtion please or where I could look for it? Many thanks -- Trish "Gary Keramidas" wrote: if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Click on the word Split anywhere within the Immediate window or within a
code window (type it if it is not already there) and press F1. This technique applies to any VB keyword. Rick "Trish Smith" wrote in message ... Thank you Gary, Could you explain the syntax for the Split funtion please or where I could look for it? Many thanks -- Trish "Gary Keramidas" wrote: if there is a space separating, then names, try this: Sub test() Dim i As Long Dim lastrow As Long Dim ws As Worksheet Set ws = Worksheets("sheet1") lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To lastrow With ws.Range("A" & i) .Offset(, 1).Value = Split(.Value)(0) .Offset(, 2).Value = Split(.Value)(1) .Offset(, 3).Value = Split(.Value)(2) End With Next End Sub -- Gary "Trish Smith" wrote in message ... Hi there, I thought perhaps I should let you know what I've been thinking so far in steps rather than code - code that i've got to work is very sparse and not sure of 1.Select cell to right of first cell in range using input box 2. use for next loop to insert columns 3. select range column b using usedrange property 4. use functions to select firstname from col A - no idea how to do this bit 5. select range col c etc etc Doesn't look like much working out when it's written down on the screen but believe me I'm just happy to have worked out steps and some bits of code -- Trish "Trish Smith" wrote: Hi everyone, 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)) Surname - =RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) I've been struggling with this all day long but now I'm going backwards. If someone could help me with this it would be great because I think that having spent so long working out what bits I need it would be the best way to learn - hope I'm not being too cheeky ! Many thanks -- Trish |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Per Trish Smith:
I've been struggling with this all day long Don't feel like the Lone Ranger. I find this problem quite a bit more difficult than it looks at first glance. Maybe even impossible without some lookup tables. Melvin Koznowski Melvin Koznowski I Melvin Koznowski Jr Melvin Koznowski Phd An Di An Di II Joe DiDonato Joe Di Donato Pat McGregor Pat Mc Gregor Pat MacGregor Pat Mac Gregor and on... and on.... and on... -- PeteCresswell |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split fullname into First Middle and Last
Hi Pete,
You're right I was feeling like the Lone Ranger and I should have asked for help earlier :-). It looks so simple when the gurus do it! -- Trish "(PeteCresswell)" wrote: Per Trish Smith: I've been struggling with this all day long Don't feel like the Lone Ranger. I find this problem quite a bit more difficult than it looks at first glance. Maybe even impossible without some lookup tables. Melvin Koznowski Melvin Koznowski I Melvin Koznowski Jr Melvin Koznowski Phd An Di An Di II Joe DiDonato Joe Di Donato Pat McGregor Pat Mc Gregor Pat MacGregor Pat Mac Gregor and on... and on.... and on... -- PeteCresswell |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Citrix and FullName | Excel Programming | |||
Split one cell in the middle and fill each with one color | Excel Worksheet Functions | |||
FullName vs FullNameURLEncoded | Excel Programming | |||
ThisWorkbook.FullName | Excel Programming | |||
Split fullname into Drive, Path and Filename | Excel Programming |