Shifting Last Name To Another Column

[AdGuy]

Hi Folks,

This is an easy one for you but not for me. In a mailing list, I have a large group of names in the "D" column. A sample of records looks like this:

Ronald S Bradshaw
Timmy Whitaker
Travis Camp
A.L. Handy
Aaron "Kyle" Sapp
Adam Mathis
Addie Roseboro
Adila Abdul-Karim
Adria Michelle Walker

I'm looking to shift just the last name to column "E", leaving the first and possibly middle name/initial behind in column "D". I'd also need to remove the ending space that was originally before the last name. What's the magic formula for this?

AdGuy

[Domenic]

Try the following...

E1, copied down:

=RIGHT(A1,LEN(A1)-LARGE(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ",ROW(INDIRECT("1:"&LEN(A1)))),1))

...confirmed with CONTROL+SHIFT+ENTER.

Then, select this new range of cells > Copy > Paste Special > Values > OK.

In cases where extra spaces may be present, try the following...

=RIGHT(TRIM(A1),LEN(TRIM(A1))-LARGE(IF(MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1)))),1)=" ",ROW(INDIRECT("1:"&LEN(TRIM(A1))))),1))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi Folks,

This is an easy one for you but not for me. In a mailing list, I have a large group of names in the "D" column. A sample of records looks like this:

Ronald S Bradshaw
Timmy Whitaker
Travis Camp
A.L. Handy
Aaron "Kyle" Sapp
Adam Mathis
Addie Roseboro
Adila Abdul-Karim
Adria Michelle Walker

I'm looking to shift just the last name to column "E", leaving the first and possibly middle name/initial behind in column "D". I'd also need to remove the ending space that was originally before the last name. What's the magic formula for this?

AdGuy

[AdGuy]

Domenic,

Thanks for the suggestion. When I tried either of the two formula recommendations, I just got the same name. Any ideas on what I or the recommended formulas might not be doing right?

AdGuy

[Domenic]

Domenic,

Thanks for the suggestion. When I tried either of the two formula recommendations, I just got the same name. Any ideas on what I or the recommended formulas might not be doing right?

AdGuy

You need to confirm the formula with CONTROL+SHIFT+ENTER. That means, after typing out the formula, instead of pressing just ENTER, press the CONTROL and SHIFT keys together, then while they're both pressed, hit the ENTER key. Excel will then place braces {} around the formula, indicating that you've entered the formula correctly.

Hope this helps!

[AdGuy]

Domenic,

Thanks for the additional instruction...it's amazing how when I do EXACTLY what you tell me it works like a charm!! A big thanks for both your information and your patience!!

AdGuy