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

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

This does it all from code -
Sub SplitByUcase()
Dim rawText As String
Dim RowOffset As Long
Dim CC As Integer ' Character Counter
'presume text is in Column A
'continuous from top to end of list
'put first name in B, Last name in C
RowOffset = Range("A65536").End(xlUp).Row - 1
Do While RowOffset = 0
rawText = Range("A1").Offset(RowOffset, 0)
If Len(rawText) 1 Then
For CC = 2 To Len(rawText)
If Mid$(rawText, CC, 1) < "a" Then
'we have found it
Range("A1").Offset(RowOffset, 1) = Left$(rawText, CC - 1)
Range("A1").Offset(RowOffset, 2) = Right(rawText,
(Len(rawText) - CC) + 1)
Exit For
End If
Next
End If
RowOffset = RowOffset - 1
Loop
End Sub

"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

With your list in column A, this code should put the first name in column B
and the last in column C:

Make a back-up copy of your workbook.
Press Ctrl and F11 to open the Visual Basic Editor.
Select Insert and then Module.
Copy the code below and paste it into the module.
Close the Editor.
Go to Tools Macro Macros€¦
Highlight the macro and click Run.

---------------------------------


Sub SeparateNames()

Dim NumRows As Double
Dim Iloc As Integer
Dim Iloop As Double
Dim Iloop1 As Integer
NumRows = Range("A65536").End(xlUp).Row
For Iloop = 1 To NumRows
For Iloop1 = 2 To Len(Cells(Iloop, "A"))
If Mid(Cells(Iloop, "A"), Iloop1, 1) = _
UCase(Mid(Cells(Iloop, "A"), Iloop1, 1)) Then
Cells(Iloop, "B") = Left(Cells(Iloop, "A"), Iloop1 - 1)
Cells(Iloop, "C") = Right(Cells(Iloop, "A"), _
Len(Cells(Iloop, "A")) - Iloop1 + 1)
Exit For
End If
Next Iloop1
Next Iloop

End Sub

--
Ken Hudson


"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

That was an amazing experience! Thanks to all who took the time to
respond - Mr. Hudson, I used your explicit instructions as I am a bit
of a novice. Hope I can contribute like that some day!


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

drewannie, My apologies. I assumed too much.

Here is a page that echo's Ken Hudson's instructions and has links to two
other pages that give instructions for special cases for code insertion:
"attaching" code to either a worksheet's or workbook's events. Perhaps one
of those will be of use to you in the futu
http://www.jlathamsite.com/Teach/Excel_GP_Code.htm

"drewannie" wrote:


That was an amazing experience! Thanks to all who took the time to
respond - Mr. Hudson, I used your explicit instructions as I am a bit
of a novice. Hope I can contribute like that some day!


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