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.


---
Regards,
Norman


"bufhal" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> bufhal
> ------------------------------------------------------------------------
> bufhal's Profile:
> http://www.excelforum.com/member.php...o&userid=10702
> View this thread: http://www.excelforum.com/showthread...hreadid=517584
>

[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
'<<=============


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> ---
> Regards,
> Norman
>
>
> "bufhal" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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.
>>
>>
>> --
>> bufhal
>> ------------------------------------------------------------------------
>> bufhal's Profile:
>> http://www.excelforum.com/member.php...o&userid=10702
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=517584
>>
>
>

[Norman Jones]

Hi Bufhal,

>> amend the value os the sSeparator variable accordingly.

Should, of course, read:

amend the value of the sSeparator constant accordingly.

---
Regards,
Norman