I have a spreadsheet with a column of cells containing concatenated first and
last names. The Last Name is denoted by a capital first letter.

For Example: BillGates

How might I separate the first and last name into separate cells?

Thanks in advance.

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

--
Regards,
Tom Ogilvy



"Al" wrote in message
...
I have a spreadsheet with a column of cells containing concatenated first
and
last names. The Last Name is denoted by a capital first letter.

For Example: BillGates

How might I separate the first and last name into separate cells?

Thanks in advance.

Wow Tom!! Thanks a BUNCH!!

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Al" wrote in message
...
I have a spreadsheet with a column of cells containing concatenated first
and
last names. The Last Name is denoted by a capital first letter.

For Example: BillGates

How might I separate the first and last name into separate cells?

Thanks in advance.