create new column with last name

[bufhal]

Hope someone can help.
I have a list of first, middle initial and last names in one
column(about 200 records). I want to pull the last name out and put it
into a new column so it can sorted by last name.
Can someone offer any guidance?
I appreciate it.

 

[Norman Jones]

Hi Bufhal,

Try something like:

'=============>>
Public Sub Tester007()
Dim rng As Range
Dim rCell As Range
Dim arr As Variant
Const sSeparator As String = " " '<<==== CHANGE

Set rng = Selection

For Each rCell In rng.Cells
With rCell
arr = Split(.Value, sSeparator)
.Offset(0, 1).Value = arr(UBound(arr))
End With
Next rCell

End Sub
'<<=============

This assumes the names are separated with a space. If the separator differs,
amend the value os the sSeparator variable accordingly.

 

[Norman Jones]

Hi Bufhal.

Preferable would be:

'=============>>
Public Sub Tester007A()
Dim rng As Range
Dim rCell As Range
Dim arr As Variant
Const sSeparator As String = " " '<<==== CHANGE

Set rng = Selection

For Each rCell In rng.Cells
With rCell
If Not IsEmpty(.Value) Then
arr = Split(.Value, sSeparator)
.Offset(0, 1).Value = arr(UBound(arr))
End If
End With
Next rCell

End Sub
'<<=============

 

[Norman Jones]

Hi Bufhal,

Should, of course, read:

amend the value of the sSeparator constant accordingly.