Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    Santa Fe, New Mexico, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reverse Concatenate? (Excel 2000)

    I know I've seen it in the lounge before, because I've done it. Unfortunately, I don't work where I used it anymore and have doubts that I saved the formula on one of my back-up discs which are tucked in my desk at home. I have a column with names formatted Last Name Comma First. I want to extract the last name and put them in their own column. I know that I want to specify anything to the left of the comma is to be extracted, but after that I'm stumped.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Concatenate? (Excel 2000)

    The easiest way to do this is the following:

    1- Insert enough empty columns to the right of the names column so that there is an empty column for each possible comma in the names.

    2- Select the column that contains the names.

    3- Select "Text To Columns..." from the data menu.

    4- In the Wizard dialog box, click on Delimited then click the Next button.

    5- In the next dialog box, select Comma as the delimiter and deselect all other delimiters.

    6- Click on the Finish button.

    Your names should now be as you wanted them.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Concatenate? (Excel 2000)

    You could do =left(a1,search(",",a1)-1). This assumes there's only 1 comma in each name. To get the first name, you could do =right(a1,len(a1)-search(",",a1))

  4. #4
    New Lounger
    Join Date
    May 2001
    Location
    Santa Fe, New Mexico, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Concatenate? (Excel 2000)

    Slick! I had completely forgotten how useful Text to Columns could be. In this instance, I had imported an ASCII file...but overlooked the comma option.

Posting Permissions

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