move to new column after ,

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I imported data from another program of my companies personnel, it came back
with all the names fine however they are listed as so.

COLUMN A COLUMN B
AGUILAR, JUAN 120
JONES, TOM 205
TORRES, SANDY 333
SMITH, JONATHAN 273

I want to move there first name into a new column to the right and have the
data in column "B" go into column "C". At the same time I would like it to
remove the "," from column "A" and the remove the space that is in front of
the first name.

Any help would be great.

Thanks
 
Sub Macro1()
Dim r As Range
For i = 1 To 100
s = Cells(i, "A").Value
If IsEmpty(s) Then
Else
l = Len(s)
l2 = InStr(1, s, " ")
Cells(i, "C").Value = Right(s, l - l2)
Cells(i, "A").Value = Left(s, l2 - 2)
End If
Next
End Sub


will convert:
Williams, Mary 1
into:
Williams 1 Mary
 
Back
Top