Split fullname into First Middle and Last

[Trish Smith]

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 Smith]

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

 

[Gary Keramidas]

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

 

[(PeteCresswell)]

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...

 

[Bob Phillips]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Trish Smith]

Thank you Gary,

Could you explain the syntax for the Split funtion please or where I could
look for it?

Many thanks

 

[Trish Smith]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

 

[Bob Phillips]

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

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 Smith]

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

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

 

[Trish Smith]

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!

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Trish Smith]

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

 

[Gary Keramidas]

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

 

[Rick Rothstein \(MVP - VB\)]

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