Clearing bad data in Excel, separating words in capital letters

Question

I am using excel 2011 on mac osx. I have a data set with about 3000 records. In the fields containing the names, many names are not divided. First and last names are separated by spaces, but individual names are grouped together.

Here I have (one cell):

Grant Morrison Soldo Fish
Ben Oliver Carlos Alberto Fernandez Urbano
Ben Oliver Carlos Alberto Fernandez Urbano
Ben Oliver
Ben Oliver

Here I want to execute (one cell, a comma, separated by a space after the comma):

Grant Morrison, Sholly Fish, Ben Oliver, Carlos Alberto, Fernandez Urbano, Ben Oliver, Carlos Alberto, Fernandez Urbano, Ben Oliver, Ben Oliver

I found several VBA scripts that will separate the words in capital letters, but the ones I tried will add spaces where I don't need them, like this one ...

Function splitbycaps(inputstr As String) As String

Dim i As Long
Dim temp As String

If inputstr = vbNullString Then
    splitbycaps = temp
    Exit Function
Else
    temp = inputstr
    For i = 1 To Len(temp)
        If Mid(temp, i, 1) = UCase(Mid(temp, i, 1)) Then
            If i <> 1 Then
                temp = Left(temp, i - 1) + " " + Right(temp, Len(temp) - i + 1)
                i = i + 1
            End If
        End If
    Next i
    splitbycaps = temp

End If
End Function

There was another one that I found here that used RegEx (forgive me, I just study all of this, so it may seem a little silly), but when I tried this, that would not work at all, and my research pointed to a way to add links to the library, which would add the necessary tools so that I can use it. Unfortunately, I can’t, for my life, find how to add a link to the library in my version of mac excel ... I can do something wrong, but this is the answer that I could not get to work ..

Function SplitCaps(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .Pattern = "([a-z])([A-Z])"
    SplitCaps = .Replace(strIn, "$1 $2")
End With
End Function

VBA excel, , , , , . !

Answer 1

Excel udpdating .

B1 A1

, , , ,

, ? ( )

Function SplitCaps(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .Pattern = "([a-z])([A-Z])"
    SplitCaps = Replace(.Replace(strIn, "$1, $2"), "<br>", ", ")
End With
End Function