Excel help question

[Wyatt]

I seem to recall last year there was something posted to flip names in a cell. Can someone help me out? I've misplaced it.
Ex.
Pujols, Albert to Albert Pujols
& also
Albert Pujols to Pujols, Albert

Please post the formula if possible

[TheRock]

Probably a few ways to do this. This will convert this "Pujols, Albert" into "Albert Pujols".

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

Going back is very similar

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

Both assume a pretty specific format.

[mamorris]

To change from "Pujols, Albert" to "Albert Pujols":

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

To do the opposite:

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

This assumes A1 is the cell with the text.

[Wyatt]

Thanks for the help

[yellowdog]

To change from "Pujols, Albert" to "Albert Pujols":

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

To do the opposite:

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

This assumes A1 is the cell with the text.

Where do you put this formula?

[TheRock]

To change from "Pujols, Albert" to "Albert Pujols":

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

To do the opposite:

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

This assumes A1 is the cell with the text.

Where do you put this formula?

Put the formula in any other cell. So if cell A1 is "Pujols, Albert" and you copy this formula to cell B1 and hit enter, B1 will then read "Albert Pujols". You can then copy the formula down an entire column to change everyone's name. Good thing to do then is select your new column, copy it, and right click, go to 'paste special' and paste values. That will get rid of the formula and end the dependency on column A which can then be deleted.

[yellowdog]

To change from "Pujols, Albert" to "Albert Pujols":

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

To do the opposite:

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

This assumes A1 is the cell with the text.

Where do you put this formula?

Put the formula in any other cell. So if cell A1 is "Pujols, Albert" and you copy this formula to cell B1 and hit enter, B1 will then read "Albert Pujols". You can then copy the formula down an entire column to change everyone's name. Good thing to do then is select your new column, copy it, and right click, go to 'paste special' and paste values. That will get rid of the formula and end the dependency on column A which can then be deleted.

Thanks!

[benjapage]

how do you guys learn this stuff? dang...

b

[JTWood]

Geez... You guys make it look complicated. Why not just Text to Columns and delimit by commas? That would put the last names in one column and the first names in another. Edit and Replace all spaces with nothing, then do a quick join, if needed, readding the space like this: =B1&" "&A1 where B1 is the first name and A1 is the last.

To each his own, right? Just as long as it gets done.

[RugbyD]

JT is right. you're making this way too hard.

1) text to columns
2) =concatenate(abc," ",xyz)