Separate Concatenated First/Last Name
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.
|