I need to change "Jane S-Doe" to "S-Doe, Jane" in the same cell. Help appreciated.

[Gamerade]

I have a bunch of names in the following format. Each name is in it's own cell

All of them have {First name> S- > Last name}

Example:
Jane S-Doe

I would like it to be: {S- > Last name, > First name}

Example:
S-Doe, Jane


Is there a way to do this automatically with a formula or macro?

 

[c_m_s_jr]

Welcome to the board!

Perhaps:

=RIGHT(A1,(LEN(A1)-FIND(" ",B1)))&", "&LEFT(A1,FIND(" ",A1))

 

[mvptomlinson]

If your names are in A1:A100, then in B1 put the formula:

=MID(A1,FIND(" ",A1)+1,255)&", "&LEFT(A1,FIND(" ",A1)-1)

Fill it down to B100, then copy B1:B100. Select A1 and use PasteSpecial -> Values to overwrite column A. Delete column B since it's no longer necessary.

 

[datadata]

Just replace A1 with the correct cell location:

=CONCATENATE(RIGHT(A1,FIND(" ",A1,1)),", ",LEFT(A1,FIND(" ",A1)-1))


*Edit: notice there's more than 1 way to skin a cat! 3 replies within minutes and each different and correct

 

[mvptomlinson]

Data, not sure how yours is "correct". It only seems to work for "Jane S-Doe" and names of the exact same length, but not names longer or shorter. And it still leaves out the comma between the last and first name.

CMS jr's just needs to add the -1 at the end to get rid of the trailing space.

 

[Gamerade]

You guys are awesome, thank you! I will report back which one worked, trying now.

 

[c_m_s_jr]

Data, not sure how yours is "correct". It only seems to work for "Jane S-Doe" and names of the exact same length, but not names longer or shorter. And it still leaves out the comma between the last and first name.

CMS jr's just needs to add the -1 at the end to get rid of the trailing space.

Your right, but instead of the -1

=TRIM(RIGHT(A1,(LEN(A1)-FIND(" ",B1)))&", "&LEFT(A1,FIND(" ",A1)))

just in case there are any extra spaces hidding out.

 

[Gamerade]

Your right, but instead of the -1

=TRIM(RIGHT(A1,(LEN(A1)-FIND(" ",B1)))&", "&LEFT(A1,FIND(" ",A1)))

just in case there are any extra spaces hidding out.

I tried this and it works, but only when I change the B1 to A1 - I am starting with the name in A1. From there I dragged down the box to match the rest of the names and it changes it correctly.

However, when I try to edit the name, the formula comes up and I can't do anything.

I also tried copy pasting to a new column, and it copies it in the original format "Jane S-Doe"

I actually need it to change the original names to this new format. I may not be putting the formula in the right place?

 

[mvptomlinson]

Did you read the last sentence in my first post:

Fill it down to B100, then copy B1:B100. Select A1 and use PasteSpecial -> Values to overwrite column A. Delete column B since it's no longer necessary.

Once you've filled the formula down column B to your last row of data in column A, select the values in column B and choose Edit -> Copy. Then click on A1 and choose Edit -> PasteSpecial -> Values. You can then delete column B (the formulas, they'll now be wrong anyway).

 

[c_m_s_jr]

You need to follow the instruction mvptomlinson left(see post #3). If you plan to use a formula approach the contents of the cell will always be the formula.

Sorry about the B1, that was a typo on my part.