Reverse Concatenate? (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote