## Have an excel question for roster and salary management

Moderator: Baseball Moderators

### Have an excel question for roster and salary management

We use excel to organize salaries and contracts for every team. I need an easy way to go through all the teams and subtract one year from each players contract. This is an example of a team.

Name Salary(\$) Contract
Paul Konerko 29 NG-4
Zach Greinke 5
Delmon Young 1 G-4
Manny Ramirez 42 G-1
John Lackey 19 NG-3
Todd Jones 3 NG-4
Josh Fields 1 NG-4
Matt Cain 4 NG-3
Scot Shields 6 NG-4
Hunter Pence 1 G-4
Chone Figgins 13 NG-2
Jeff Francoeur 3 G-4
Francisco Liriano 4 NG-3
Derrek Lee 34 NG-2
Derek Jeter 40 NG-4
James Shields 0
Rich Hill 15 NG-4
Corey Hart 1 G-4
Troy Glaus 5
Carlos Villanueva 5
Kevin Gregg 5
Eric Chavez 5
Juan Uribe 5
Felix Pie 1 G-4
Jeff Mathis 0
Jeremy Bonderman 9 G-2
Dustin McGowan 5
Jared Burton 0
Cap Hit 4
Total 265
jfox72
Softball Supervisor

Posts: 69
Joined: 24 Nov 2003
Home Cafe: Baseball

### Re: Have an excel question for roster and salary management

Ill email you a word doc with instructions, hope it helps !
mission27
Major League Manager

Posts: 1132
Joined: 12 Feb 2007
Home Cafe: Baseball
Location: Connecticut

### Re: Have an excel question for roster and salary management

could i get a copy too please
BigKahuna83
College Coach

Posts: 110
(Past Year: 1)
Joined: 16 Aug 2006
Home Cafe: Baseball
Location: Top of the Fantasy Mountain!

### Re: Have an excel question for roster and salary management

All you need is this...

=MID(A1,FIND("-",A1)+1,1)

assuming that the player you're looking at is in cell A1 and then just copy down.

Then subtract 1 off of each number. And then the formula...

=LEFT(A1,FIND("-",A1))

will give you everything but the number.

And then if your new number is in C1 and the rest of it is in D1 just...

=D1&C1

For the future, though, it's just better to keep the number of years in the contract in its own column. And then Guaranteed/Non-Guaranteed in its own column. Then you don't have to do anything except subtract one.

Note that this only works because the OP had his years in the format where he had a dash (-) before the years and so that's what it was looking for. If you don't have something like that, then it won't work.
0-3 to 4-3. Worst choke in the history of baseball. Enough said.
Matthias
General Manager

Posts: 4860
Joined: 16 Mar 2005
Home Cafe: Baseball

### Re: Have an excel question for roster and salary management

I'd just select the column with all the data in it and use "Tools -> Text to Columns" to delimit on dashes (enter it into the other field).

That will put the contract years into their own column (column B) and remove the dash. Do a formula in column C like =if(B1>0,B1-1,"") to subtract one from each player who had a multi-year contract. Make sure to copy and paste special -> values over the formula after doing that so you don't change things later.

So now you have most of the original data in column A, the original contract length in column B, and the new contract length in Column C. To put it back together in the old format, enter this formula into column D: =A1&"-"&C1. Copy that formula all the way down, copy, and paste special -> values over the final data. Voila!

JTWood
Hall of Fame Hero

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

### Re: Have an excel question for roster and salary management

yeah, text to columns is the easiest thing for me. I like to rely on as simple formulas as possible and try to make each cell only have one bit of data.
Curtis Pride
Major League Manager

Posts: 1362
(Past Year: 3)
Joined: 19 May 2007
Home Cafe: Baseball