Here is some code that will act on the selected text
Sub ParseNames()
Dim myRange As Range
Dim myArray(2), CommaCount, X As Integer
Set myRange = Selection
For Each cell In myRange
CommaCount = 0
myArray(1) = 0
myArray(2) = 0
'count commas
If Len(cell) = 0 Then GoTo ExitHe
For X = 1 To Len(cell)
If Mid(cell.Text, X, 1) = "," Then
CommaCount = CommaCount + 1
myArray(CommaCount) = X 'store position of comma
End If
Next X
If CommaCount = 1 Then
cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1)
cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) -
myArray(1) - 1)
Else
cell.Offset(0, 1).Value = Left(cell.Value, myArray(1) - 1)
cell.Offset(0, 2).Value = Right(cell.Value, Len(cell) -
myArray(2) - 1)
cell.Offset(0, 3).Value = Mid(cell.Value, myArray(2) - 1,
1)
End If
ExitHe
Next cell
End Sub
--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile:
http://www.excelforum.com/member.php...o&userid=35514
View this thread:
http://www.excelforum.com/showthread...hreadid=561786