Extracting Words from Cells

  • Thread starter Thread starter Tommy
  • Start date Start date
T

Tommy

I would like to extract the last names from a group of cells
containing a first and last name and place them into a separate
column. Is there an easier way to accomplish this other than
highlghting the last name and using cut&paste to move into the new
cell?
 
If each cell is only two names (first & last, no "Jr.", etc.) it's
pretty easy.

Dim c As Range
Dim rng As Range
Dim iPos As Integer
'change next line to suit
Set rng = Sheets("Sheet1").Range("A1:A10")
For Each c In rng
iPos = InStr(c, " ")
'extract last name and put in col B
c.Offset(0, 1) = Right(c, Len(c) - iPos)
Next c

Hth,
Merjet
 
If the first and last names are separated by a space and/or a comma, then
consider using Text to Columns
 
Back
Top