Showing alternates
Sub splitnames()
c = ActiveCell
findcomma = InStr(c, ",")
fand = InStr(c, "and")
x = Mid(c, findcomma + 1, fand - findcomma - 2) & " " & Left(c, findcomma -
1)
ActiveCell.Offset(, 1) = x
ActiveCell.Offset(, 2) = Right(c, Len(c) - fand - 2) & " " & Left(c,
findcomma - 1)
End Sub
'more than one
Sub splitnamesloop()
For Each c In Range("a2:a16")
findcomma = InStr(c, ",")
fand = InStr(c, "and")
'each line below is ONE line
c.Offset(, 1) = Mid(c, findcomma + 1, fand - findcomma - 2) & " " & Left(c,
findcomma - 1)
c.Offset(, 2) = Right(c, Len(c) - fand - 2) & " " & Left(c, findcomma - 1)
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"CCarter" wrote in message
...
Is there a way for me to change:
Smith, John and Jane
to:
John Smith Jane Smith
Thanks in advance,
Cathy