create new column with last name
 

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


--
bufhal
------------------------------------------------------------------------
bufhal's Profile: http://www.excelforum.com/member.php...o&userid=10702
View this thread: http://www.excelforum.com/showthread...hreadid=517584

create new column with last name
 

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" wrote in
message ...

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


--
bufhal
------------------------------------------------------------------------
bufhal's Profile:
http://www.excelforum.com/member.php...o&userid=10702
View this thread: http://www.excelforum.com/showthread...hreadid=517584

create new column with last name
 

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" wrote in message
...
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" wrote in
message ...

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


--
bufhal
------------------------------------------------------------------------
bufhal's Profile:
http://www.excelforum.com/member.php...o&userid=10702
View this thread:
http://www.excelforum.com/showthread...hreadid=517584

create new column with last name
 

Hi Bufhal,

amend the value os the sSeparator variable accordingly.

Should, of course, read:

amend the value of the sSeparator constant accordingly.

---
Regards,
Norman