Separate Concatenated First,Last Names
Select the cells you want processed and run:
Sub Separate()
Dim iloc As Long, i As Long
Dim cell As Range, sStr As String
For Each cell In Selection
sStr = cell.Value
i = Len(sStr) + 1
For iloc = Len(sStr) To 1 Step -1
If UCase(Mid(sStr, iloc, 1)) = Mid(sStr, iloc, 1) Then
i = iloc
Exit For
End If
Next
If i < Len(sStr) + 1 Then
cell.Value = Left(sStr, i - 1)
cell.Offset(0, 1).Value = Right(sStr, Len(sStr) - i + 1)
End If
Next
End Sub
This leaves the first name in the original cell and puts the last name in
the cell to the right.
--
Regards,
Tom Ogilvy
"Al" wrote in message
...
I have a spreadsheet which contains concatenated first and last names
within
a cell which I have to separate. The Last Name is denoted by an Upper
Case
First Letter.
An example is BillGates
How might a separate the first and last names into separate cells?
Thanks in advance.
|