Separate Concatenated First,Last Names

[Guest]

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.

 

[Tom Ogilvy]

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.