Split name in a column without spaces or commas

[LGF]

I have a list of 1st and last names in a column that I would like to split into two columns. The 1st and last names are not separated by a space or comma - they are only identifiable by capital letters (eg. SteveSmith). How can I break up the names?

 

[jindon]

Hi
UDF if you like
1) hit Alt + F11 To open VB Editor
2) go to [Insert] - [Module] then paste the code there
3) close the window to get back to excel
If data is in A1 then select B1:C1(or any horizontal consecutive 2 cells) and enter
=FLName(A1)
Confirm with Ctrl + Shift + Enter

Function FLName(txt As String)
Dim FName As String, LName As String, i As Integer, mItem As Object
With CreateObject("VBScript.RegExp")
   .Pattern = "[A-Z][a-z]+"
   .Global = True
   Set mItem = .execute(txt)
   If mItem.count>1 Then
      FName = mItem(0)
      For i = 2 To mItem.count
         LName = LName & mItem(i-1)
      Next
   Else
      FName = txt
   End If
End With
FLName = Array(FName,LName)
End Function