+ Reply to Thread
Results 1 to 6 of 6

Separate a FirstLast text string into two columns

  1. #1
    Registered User
    Join Date
    07-05-2006
    Posts
    4

    Separate a FirstLast text string into two columns

    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!

  2. #2
    Toppers
    Guest

    RE: Separate a FirstLast text string into two columns

    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
    >
    >


  3. #3
    JLatham
    Guest

    RE: Separate a FirstLast text string into two columns

    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
    >
    >


  4. #4
    Ken Hudson
    Guest

    RE: Separate a FirstLast text string into two columns

    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
    >
    >


  5. #5
    Registered User
    Join Date
    07-05-2006
    Posts
    4

    Separate a FirstLast text string into two columns

    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!

  6. #6
    JLatham
    Guest

    Re: Separate a FirstLast text string into two columns

    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 future:
    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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1