Excel help question - Fantasy Baseball Cafe 2014

## Excel help question

Moderator: Baseball Moderators

### Excel help question

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
Wyatt
College Coach

Posts: 202
(Past Year: 1)
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

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.
TheRock
General Manager

Posts: 3055
(Past Year: 6)
Joined: 16 Apr 2003
Home Cafe: Baseball
Location: America's Heartland

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.
mamorris
College Coach

Posts: 299
Joined: 8 Feb 2005

Thanks for the help
Wyatt
College Coach

Posts: 202
(Past Year: 1)
Joined: 28 Jan 2005
Home Cafe: Baseball
Location: Tennessee

mamorris wrote: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?
"It's like a full-body dry-heave set to music"
yellowdog
College Coach

Posts: 196
Joined: 16 Jan 2003
Home Cafe: Baseball
Location: Chillicothe, Ohio

yellowdog wrote:
mamorris wrote: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.
TheRock
General Manager

Posts: 3055
(Past Year: 6)
Joined: 16 Apr 2003
Home Cafe: Baseball
Location: America's Heartland

TheRock wrote:
yellowdog wrote:
mamorris wrote: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!
"It's like a full-body dry-heave set to music"
yellowdog
College Coach

Posts: 196
Joined: 16 Jan 2003
Home Cafe: Baseball
Location: Chillicothe, Ohio

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

b
yeah, yes...
benjapage
General Manager

Posts: 2767
Joined: 26 May 2003
Home Cafe: Baseball
Location: Cincinnati, OH

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.

JTWood
Hall of Fame Hero

Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

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

1) text to columns
2) =concatenate(abc," ",xyz)
TennCare rocks!!!!
RugbyD
Hall of Fame Hero

Posts: 5591
Joined: 7 Dec 2004
Home Cafe: Baseball
Location: punting small dogs and being surly

Next

### Who is online

Users browsing this forum: Exabot [Bot], scarnicease and 8 guests

Forums Articles & Tips Sleepers Rankings Leagues

Today's Games
Friday, Aug. 29
(All times are EST, weather icons show forecast for game time)

 • Minnesota at Baltimore(7:05 pm)
 • Cincinnati at Pittsburgh(7:05 pm)
 • NY Yankees at Toronto(7:07 pm)
 • Philadelphia at NY Mets(7:10 pm)
 • Boston at Tampa Bay(7:10 pm)
 • Miami at Atlanta(7:35 pm)
 • Cleveland at Kansas City(8:10 pm)
 • Texas at Houston(8:10 pm)
 • Detroit at Chi White Sox(8:10 pm)
 • Chi Cubs at St. Louis(8:15 pm)
 • Oakland at LA Angels(10:05 pm)
 • LA Dodgers at San Diego(10:10 pm)
 • Washington at Seattle(10:10 pm)
 • Milwaukee at San Francisco(10:15 pm)