moving last word from one column to another?

[dt42645]

Hello,
Basically I have a list of peoples first and last that I am trying to import into another application. My problem is that the application wants the first names in column A and the last names in column B. Right now my excel has the first and last names in the A column. I would just copy and paste, but there are 2,600! I just want to move the last word in column A to column B...any ideas?

Thanks!
Laura

[NBVC]

If you only have First/Last names and no middle names/initials, then...


select column A and go to Data |Text to columns.

Select Delimited and click Next

Select Space and click Finish.

[jasoncw]

Hi, Laura. If there is only one space in the cell, you can use Data > Text to Columns, use delimited, and use a space as the delimiter. This will split the first and last names. Be sure column B is blank before doing this, though.

HTH

Jason

[dt42645]

That almost worked but some of the names have one first name and some have two and several have &.

Example:
First Name
Kathy Acker
Stan & Irene Ackers
Jessie & Ofelia Acosta
Richard & Mildred Adamek
Donna Adams
Doris Adams
Jerry & Jan Adams

Anyway to fix that? I wish there was a way to just move the last word into the next column.

[oldchippy]

Try this, your text in A1 and this in B1

=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

[NBVC]

That almost worked but some of the names have one first name and some have two and several have &.

Example:
First Name
Kathy Acker
Stan & Irene Ackers
Jessie & Ofelia Acosta
Richard & Mildred Adamek
Donna Adams
Doris Adams
Jerry & Jan Adams

Anyway to fix that? I wish there was a way to just move the last word into the next column.

Try:

=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))

in B1 copied down

[dt42645]

That worked but only for the first row. I did highlight all of column B.

[NBVC]

That worked but only for the first row. I did highlight all of column B.

Don't highlight anything.

Select Cell B1 and enter the formula....then copy it down the column by double-clicking on the little black square in the bottom right corner of cell B1.

[dt42645]

WOW that worked!! You guys are geniuses!! Now is there anyway to take off the last names in the first column?

[NBVC]

WOW that worked!! You guys are geniuses!! Now is there anyway to take off the last names in the first column?

In 2 steps:

In C1, enter formula: =SUBSTITUTE(A1,B1,"") and copy it down.

Now select Column C and copy it, then go to A1 and select Paste Special from the Edit menu. Select Values. Click Ok.

Now you can delete column C.

[oldchippy]

In C1 put this

=LEFT(A1,LEN(A1)-LEN(B1)-1)

[dt42645]

Ok that all worked. Thank you so much...you saved me at least 3 hours!

[oldchippy]

Glad to help - thanks for the feedback

[NBVC]

Ok that all worked. Thank you so much...you saved me at least 3 hours!

You;re welcome!

[LumsdenDouglas]

Hi Guys


sorry to bug you all but upon pasting the formula =MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1))

I don't seem to be able to get the A1 fill down to update to A2 etc.

Any ideas on how to fix that?

[shukla.ankur281190]

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.