Separate Concatenated First/Last Name

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Myrna Larson]

If you don't have a separator like a space or comma and the first names vary
in length, you can't use Data/Text to columns, so a VBA macro might help.

After you've copied the code to a standard module in your workbook, you would
write the worksheet formula as below. The first argument is literal text or
the reference to a cell containing text, the 2nd argument is which "piece" you
want, with a "piece" starting with an upper case letter and ending with the
character preceding the next upper case letter.

=SplitName("BillHGates",1) 'Bill
=SplitName("BillHGates",2) 'H
=SplitName("BillHGates",3) 'Gates

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Option Compare Binary

Function SplitName(sText As String, Which As Long) As String
Dim i As Long
Dim j As Long
Dim p1 As Long
Dim p2 As Long
Dim sTemp As String

SplitName = ""
If Which < 1 Or Which > Len(sText) Then Exit Function

sTemp = sText & "Z"
j = 0

For i = 1 To Len(sTemp)
Select Case Mid$(sTemp, i, 1)
Case "A" To "Z"
j = j + 1
If j = Which Then
p1 = i
ElseIf j = Which + 1 Then
p2 = i
Exit For
End If
End Select
Next i

If p1 > 0 And p2 > 0 Then SplitName = Mid$(sText, p1, p2 - p1)

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Guest]

Wow Tom!! Thanks a BUNCH!!

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

 

[Guest]

Thanks for your help Myrna!

If you don't have a separator like a space or comma and the first names vary
in length, you can't use Data/Text to columns, so a VBA macro might help.

After you've copied the code to a standard module in your workbook, you would
write the worksheet formula as below. The first argument is literal text or
the reference to a cell containing text, the 2nd argument is which "piece" you
want, with a "piece" starting with an upper case letter and ending with the
character preceding the next upper case letter.

=SplitName("BillHGates",1) 'Bill
=SplitName("BillHGates",2) 'H
=SplitName("BillHGates",3) 'Gates

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit
Option Compare Binary

Function SplitName(sText As String, Which As Long) As String
Dim i As Long
Dim j As Long
Dim p1 As Long
Dim p2 As Long
Dim sTemp As String

SplitName = ""
If Which < 1 Or Which > Len(sText) Then Exit Function

sTemp = sText & "Z"
j = 0

For i = 1 To Len(sTemp)
Select Case Mid$(sTemp, i, 1)
Case "A" To "Z"
j = j + 1
If j = Which Then
p1 = i
ElseIf j = Which + 1 Then
p2 = i
Exit For
End If
End Select
Next i

If p1 > 0 And p2 > 0 Then SplitName = Mid$(sText, p1, p2 - p1)

End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~