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

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

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

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

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

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

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

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

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

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

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

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

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

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

On Mon, 11 Aug 2008 05:26:31 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Since all you are doing is splitting on <space, would it be even more
efficient to merely execute the "text-to-columns" routine?


e.g.:

Sub ParseName()
Selection.TextToColumns _
Destination:=Selection.Offset(, 1), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
End Sub
--ron

That is a good question. The TextToColumns method has a lot of arguments and
all those arguments means there is lots of code behind the scenes checking,
filtering and even executing parsing code, possibly even if a particular
underlying code section is not required; so, the answer depends on how
optimized the TextToColumns method is within the VB world. I'm thinking of
something like the Like operator here... it is not particularly "fast" even
during its most simplest form (e.g., Variable Like "A*")... it appears to go
through lots of behind the scenes "motions" just in case they apply which
tends to slow it down even if it does not have a lot to do. If TextToColumns
does something like that too, then it might not be as fast as it seems it
should be, even for a simple split condition. (Maybe some time test are in
order here?)

Rick


"Ron Rosenfeld" wrote in message
...
On Mon, 11 Aug 2008 05:26:31 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Since all you are doing is splitting on <space, would it be even more
efficient to merely execute the "text-to-columns" routine?


e.g.:

Sub ParseName()
Selection.TextToColumns _
Destination:=Selection.Offset(, 1), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
End Sub
--ron

Hi Gurus

I've just realised reading Ron's Text to Columns post that I'm going to have
problems with middle name and surname.

Surname will get placed in the middle name column in names that don't have a
middle name. I'm sorry that I didn't realise until now but I checked out the
code on a spreadsheet with names in Surname Middle and Firstname order and it
didn't show up.

I was prompted to check by text to cols 'cos I stopped using that function
because it mixed surnames and middle names.

I'm going to post again - sorry to be a pain

Thank you
--
Trish

"Rick Rothstein (MVP - VB)" wrote:

That is a good question. The TextToColumns method has a lot of arguments and
all those arguments means there is lots of code behind the scenes checking,
filtering and even executing parsing code, possibly even if a particular
underlying code section is not required; so, the answer depends on how
optimized the TextToColumns method is within the VB world. I'm thinking of
something like the Like operator here... it is not particularly "fast" even
during its most simplest form (e.g., Variable Like "A*")... it appears to go
through lots of behind the scenes "motions" just in case they apply which
tends to slow it down even if it does not have a lot to do. If TextToColumns
does something like that too, then it might not be as fast as it seems it
should be, even for a simple split condition. (Maybe some time test are in
order here?)

Rick


"Ron Rosenfeld" wrote in message
...
On Mon, 11 Aug 2008 05:26:31 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Since all you are doing is splitting on <space, would it be even more
efficient to merely execute the "text-to-columns" routine?


e.g.:

Sub ParseName()
Selection.TextToColumns _
Destination:=Selection.Offset(, 1), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
End Sub
--ron

i'll modify my own code with some of the suggestions .
see if this works

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 Gurus

I've just realised reading Ron's Text to Columns post that I'm going to have
problems with middle name and surname.

Surname will get placed in the middle name column in names that don't have a
middle name. I'm sorry that I didn't realise until now but I checked out the
code on a spreadsheet with names in Surname Middle and Firstname order and it
didn't show up.

I was prompted to check by text to cols 'cos I stopped using that function
because it mixed surnames and middle names.

I'm going to post again - sorry to be a pain

Thank you
--
Trish

"Rick Rothstein (MVP - VB)" wrote:

That is a good question. The TextToColumns method has a lot of arguments and
all those arguments means there is lots of code behind the scenes checking,
filtering and even executing parsing code, possibly even if a particular
underlying code section is not required; so, the answer depends on how
optimized the TextToColumns method is within the VB world. I'm thinking of
something like the Like operator here... it is not particularly "fast" even
during its most simplest form (e.g., Variable Like "A*")... it appears to go
through lots of behind the scenes "motions" just in case they apply which
tends to slow it down even if it does not have a lot to do. If TextToColumns
does something like that too, then it might not be as fast as it seems it
should be, even for a simple split condition. (Maybe some time test are in
order here?)

Rick


"Ron Rosenfeld" wrote in message
...
On Mon, 11 Aug 2008 05:26:31 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Since all you are doing is splitting on <space, would it be even more
efficient to merely execute the "text-to-columns" routine?


e.g.:

Sub ParseName()
Selection.TextToColumns _
Destination:=Selection.Offset(, 1), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
End Sub
--ron

Here is a slight modification 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
.. .
i'll modify my own code with some of the suggestions .
see if this works

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 Gurus

I've just realised reading Ron's Text to Columns post that I'm going to
have
problems with middle name and surname.

Surname will get placed in the middle name column in names that don't
have a
middle name. I'm sorry that I didn't realise until now but I checked out
the
code on a spreadsheet with names in Surname Middle and Firstname order
and it
didn't show up.

I was prompted to check by text to cols 'cos I stopped using that
function
because it mixed surnames and middle names.

I'm going to post again - sorry to be a pain

Thank you
--
Trish

"Rick Rothstein (MVP - VB)" wrote:

That is a good question. The TextToColumns method has a lot of arguments
and
all those arguments means there is lots of code behind the scenes
checking,
filtering and even executing parsing code, possibly even if a particular
underlying code section is not required; so, the answer depends on how
optimized the TextToColumns method is within the VB world. I'm thinking
of
something like the Like operator here... it is not particularly "fast"
even
during its most simplest form (e.g., Variable Like "A*")... it appears
to go
through lots of behind the scenes "motions" just in case they apply
which
tends to slow it down even if it does not have a lot to do. If
TextToColumns
does something like that too, then it might not be as fast as it seems
it
should be, even for a simple split condition. (Maybe some time test are
in
order here?)

Rick


"Ron Rosenfeld" wrote in message
...
On Mon, 11 Aug 2008 05:26:31 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Since all you are doing is splitting on <space, would it be even more
efficient to merely execute the "text-to-columns" routine?


e.g.:

Sub ParseName()
Selection.TextToColumns _
Destination:=Selection.Offset(, 1), _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1))
End Sub
--ron