+ Reply to Thread
Results 1 to 4 of 4

Separate cell text FirstnameLastname into two columns

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

    Separate cell text FirstnameLastname into two columns

    How can I separate a column of cells containing a single text string in each[these are names formated as a string with no delimiter as in "FirstLast" with the first letter of first and last name capitalized and the rest lower case...] into two columns of text called Firstname and Lastname?

    Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    Hi

    To separate one column in 2. Assuming Column A contains Lastname, firstname. Column B must be empty if not insert a column to create and empty columnB.

    Once completed click on column A (this should highlight Columns), from there go to menu ---->Data ---->Text to Columns... Choose the option Delimited and then click on next choose the option space. Click on next and then finish and everything should be separated into 2 columns.

    THanks
    Denis

  3. #3
    Valued Forum Contributor
    Join Date
    12-16-2004
    Location
    Canada, Quebec
    Posts
    363
    Hi

    I forgot if the name in the cell

    follow this format (no space between firstname and lastname)
    JohnSmith
    MarySmith
    BabySmith

    Assuming the name are in column A and column B is empty you could you use the following code:

    Sub separate_firstname_lastname()
    Dim trouve_ucase As String
    flag1 = "NO"
    rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
    For i = 1 To rowcount
    Range("a" & i).Select
    entirename = ActiveCell.Value
    check_len = Len(entirename)
    check_len = check_len
    For j = 1 To check_len
    trouve_ucase = Mid(entirename, check_len - j, 1)
    If trouve_ucase = "A" Or trouve_ucase = "B" Or trouve_ucase = "C" Or trouve_ucase = "D" _
    Or trouve_ucase = "E" Or trouve_ucase = "F" Or trouve_ucase = "G" Or trouve_ucase = "H" _
    Or trouve_ucase = "I" Or trouve_ucase = "J" Or trouve_ucase = "K" Or trouve_ucase = "L" _
    Or trouve_ucase = "M" Or trouve_ucase = "N" Or trouve_ucase = "O" Or trouve_ucase = "P" _
    Or trouve_ucase = "Q" Or trouve_ucase = "R" Or trouve_ucase = "S" Or trouve_ucase = "T" _
    Or trouve_ucase = "U" Or trouve_ucase = "V" Or trouve_ucase = "W" Or trouve_ucase = "X" _
    Or trouve_ucase = "Y" Or trouve_ucase = "Z" And flag1 = "NO" Then

    number1 = (check_len - j)
    flag1 = "YES"
    firstname = Left(entirename, number1 - 1)
    lastname = Right(entirename, number1 - 1)
    ActiveCell = firstname
    ActiveCell.Offset(0, 1).Select
    ActiveCell = lastname
    GoTo line1:
    End If
    Next j
    line1:
    Next i
    End Sub

  4. #4
    Ron Rosenfeld
    Guest

    Re: Separate cell text FirstnameLastname into two columns

    On Wed, 5 Jul 2006 15:08:42 -0500, drewannie
    <[email protected]> wrote:

    >
    >How can I separate a column of cells containing a single text string in
    >each[these are names formated as a string with no delimiter as in
    >"FirstLast" with the first letter of first and last name capitalized
    >and the rest lower case...] into two columns of text called Firstname
    >and Lastname?
    >
    >Thanks!


    Download and install Longre's free morefunc.xll add-in from
    http://xcell05.free.fr/

    Then with

    A1: FirstnameLastname

    Firstname: =REGEX.MID(A1,"[A-Z][^A-Z]+")
    Lastname: =REGEX.MID(A1,"[A-Z][^A-Z]+",2)



    --ron

+ 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