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

Gamerade

New Member
Joined
Oct 29, 2009
Messages
4
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?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
Welcome to the board!

Perhaps:

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

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
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

New Member
Joined
Oct 15, 2009
Messages
39
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

Well-known Member
Joined
Mar 10, 2008
Messages
2,638

ADVERTISEMENT

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

New Member
Joined
Oct 29, 2009
Messages
4
You guys are awesome, thank you! I will report back which one worked, trying now.
 

c_m_s_jr

Well-known Member
Joined
Mar 23, 2009
Messages
1,561

ADVERTISEMENT

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

New Member
Joined
Oct 29, 2009
Messages
4
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

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
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

Well-known Member
Joined
Mar 23, 2009
Messages
1,561
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,530
Messages
5,596,700
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top