The UDF below will separate names by a blank so:

In A1=JohnDavies
In B1 =SpliTname(A1) will give John Davies
In C1: =LEFT(B1,FIND(" ",B1)-1) .....John
In D1: =RIGHT(B1,LEN(B1)-FIND(" ",B1)).....Davies

Does this help?


Function SplitName(ByVal rng As Range) As String
Dim txt As String
Dim i As Integer
txt = rng
For i = 2 To Len(txt)
If Mid(txt, i, 1) = UCase(Mid(txt, i, 1)) Then
txt = Left(txt, i - 1) & " " & Mid(txt, i, 255)
Exit For
End If
Next
SplitName = txt
End Function

"drewannie" wrote:


Please advise on how to separate a text string that has no delimiters?
The cells contain names without spaces or commas in this format:
FirstnameLastname. The result I want is two columns with firstname in
one and lastname in the second column. The only thing distinguishing
the two desired fields within the string is an upper case letter at the
beginning of first and last names.

Thanks! :)


--
drewannie
------------------------------------------------------------------------
drewannie's Profile: http://www.excelforum.com/member.php...o&userid=36079
View this thread: http://www.excelforum.com/showthread...hreadid=558657