Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Separating Text in a cell (Excel XP)

    We have a database that has a first name and a last name in once cell, separated by a comma. Is there any formula that will let us separate the first name and last name into two separate cells. I thought of using left or right but the number of characters in the first and last names is not consistent.

    Thanks in advance for your help.

    Christa

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Separating Text in a cell (Excel XP)

    The following macro will split names. Paste the code into a module, select the cells with the names, then run the macro.

    Sub SplitOnComma()
    On Error GoTo Exit_Sub
    Dim aCell As Range
    ' Select one column
    Selection.Columns(1).Select
    ' Insert a column
    Selection.Offset(0, 1).Insert xlShiftToRight
    ' Splits cells
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
    Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1))
    ' Remove superfluous spaces
    For Each aCell In Selection
    aCell = Trim(aCell)
    aCell.Offset(0, 1) = Trim(aCell.Offset(0, 1))
    Next
    Exit_Sub:
    Set aCell = Nothing
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Separating Text in a cell (Excel XP)

    <P ID="edit" class=small>(Edited by sdckapr on 29-Jul-05 15:50. added PS)</P>If A1 has the text like "Last, First"

    Then this will give the last name:
    <pre>=LEFT(A1,FIND(",",A1)-1)</pre>


    And this will give the first:
    <pre>=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)</pre>


    Steve
    PS Data - Text to columns can also parse it without code or formulas

Posting Permissions

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