Excel help question - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Excel help question

Moderator: Baseball Moderators

Excel help question

Postby Wyatt » Sun Feb 05, 2006 1:49 am

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


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

Postby TheRock » Sun Feb 05, 2006 2:58 am

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

User avatar
CafeholicCafe WriterMock(ing) Drafter
Posts: 3055
(Past Year: 6)
Joined: 16 Apr 2003
Home Cafe: Baseball
Location: America's Heartland

Postby mamorris » Sun Feb 05, 2006 2:58 am

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


Posts: 299
Joined: 8 Feb 2005
Home Cafe: Basketball

Postby Wyatt » Sun Feb 05, 2006 2:56 pm

Thanks for the help
Wyatt
College Coach
College Coach


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

Postby yellowdog » Fri Feb 10, 2006 2:24 pm

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

User avatar

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

Postby TheRock » Fri Feb 10, 2006 2:31 pm

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

User avatar
CafeholicCafe WriterMock(ing) Drafter
Posts: 3055
(Past Year: 6)
Joined: 16 Apr 2003
Home Cafe: Baseball
Location: America's Heartland

Postby yellowdog » Fri Feb 10, 2006 10:39 pm

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

User avatar

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

Postby benjapage » Sat Feb 11, 2006 12:05 am

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

b
yeah, yes...
benjapage
General Manager
General Manager

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

Postby JTWood » Sat Feb 11, 2006 3:04 am

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.

:-)
Image
JTWood
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicCafe WriterEagle EyeWeb Supporter
Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

Postby RugbyD » Sat Feb 11, 2006 3:31 am

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
Hall of Fame Hero

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

Next

Return to Baseball Leftovers

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)
indoors
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)
Colorado at Arizona
(9:40 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)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact