Separate text

[Sigh]

I have a list of first and last names names which run into each other in one text string e.g. JohnSmith, DavidJones etc., is there a formula to separate these out into two columns - John Smith, David Jones?

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use...

Function GetName(S As String, Ordinal As Long) As String
  Dim X As Long, SplitPoint As Long
  For X = 2 To Len(S)
    If Mid(S, X, 1) = UCase(Mid(S, X, 1)) Then Exit For
  Next
  If Ordinal = 1 Then
    GetName = Left(S, X - 1)
  ElseIf Ordinal = 2 Then
    GetName = Mid(S, X)
  End If
End Function

The first argument is the squished together name and the second argument is either a 1 for the first name or 2 for the last name. So, if your name is in Column A starting on Row 1, put these formulas in the indicated cells and copy them down to the end of your data...

B1: =GetName(A1,1)

C1: =GetName(A1, 2)


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function as shown above

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Sigh]

Thanks for replying, I've followed your instruction but the result is #VALUE ! each time, I must be doing something wrong.

 

[Peter_SSs]

Here is another UDF (implemented as described by Rick) that will handle a few more varied circumstances as shown below.

Function PartName(s As String, Num As Long) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(.+[a-z])([A-Z].+)"
    PartName = Split(.Replace(s, "$1 $2"))(Num - 1)
  End With
End Function

Each formula below is copied down.

Excel Workbook
	A	B	C
1	JohnSmith	John	Smith
2	Mary-AnnSmith	Mary-Ann	Smith
3	MaryFullerton-Smith	Mary	Fullerton-Smith
4	Mary-AnnFullerton-Smith	Mary-Ann	Fullerton-Smith
Sheet1

Note though that this sort of problem is always difficult to cover all situations. For example, my solution would also fail for "Tom de Groot" merged to "TomdeGroot" or "John van den Burg" merged to "JohnvandenBurg" as there is no logical way to determine where/how that sort of name should be split.

 

[Sigh]

This is perfect Peter_SSs, thank you!

 

[Peter_SSs]

This is perfect Peter_SSs, thank you!

You are welcome, though I hope you noted my final comments.