Add characters between...

I have a string of names that run together without spaces or commas between each name.

"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.

Is there a way to add a comma and space when an upper case letter is immediately followed by a lower case letter?

I don't know of a simple way without adding a special function into your sheet. Here's a User-Defined Function (UDF) that will do it for you.

Function NameSplit(Rng As Range, Delim As String)
'JBeaucaire (9/14/2009)
'Comma-delimits a continuous string of names
If Rng.Cells.Count > 1 Then
    NameSplit = "1 cell only"
    Exit Function
End If

Dim i As Long, buf As String

buf = Left(Rng.Text, 1)

    For i = 2 To Len(Rng.Text)
        Select Case Mid(Rng.Text, i, 1)
            Case "A" To "Z"
                Select Case Mid(Rng.Text, i - 1, 1)
                    Case "a" To "z"
                        buf = buf & Delim & Mid(Rng.Text, i, 1)
                    Case Else
                        buf = buf & Mid(Rng.Text, i, 1)
                End Select
            Case Else
                buf = buf & Mid(Rng.Text, i, 1)
        End Select
    Next i
    
NameSplit = buf
End Function

==========
How to install the User Defined Function:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function is installed and ready to use.
=========

If the string were in A1, then put this formula in an adjacent cell:
=NAMESPLIT(A1, ", ")

The first parameter is the cell
The second parameter is the text string you've chose to split the names.

My pleasure, and you're welcome. I wish I always remembered...

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)