Have an excel question for roster and salary management

[jfox72]

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

[mission27]

Ill email you a word doc with instructions, hope it helps !

[BigKahuna83]

could i get a copy too please

[Matthias]

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.

[JTWood]

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!

[Curtis Pride]

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.