[Jun 29] Hawks beat Lions to pad out PL lead

Background: I've got a spreadsheet where I just copy and paste baseball stats from Japanese baseball, where I have separate sheets for automatically sorting the top 10 players for stats like average, HR's, RBI's, strike outs etc. Those were all pretty easy to figure out as they are just numbers and I used a combination of RANK, MATCH, and INDEX to sort out the top 10 into lists I can just copy and paste in English (with reddit markdown) into a wiki tab for daily updates. (note: I don't know how to use VBA or macros)

My problem lies with the innings pitched stats. The source I copy from displays the innings pitched as numbers as well, but if the pitcher doesn't have a full inning, it will display the number as 105 1/3 or 105 2/3. (note: Yahoo Japan displays it as a fraction, not as 105.33 or 105.67, and there is a space in there). I tried using =LEFT(C2, SEARCH(" ",$C$2)) to separate out the players with partial innings, where C2 is the innings pitched stat, and that works when the stat is 3 digits, but if the pitcher only has double digit innings, the above LEFT formula also returns the first part of the fraction as well. [ie 87 1/3 returns the value 87 1] (I'm guessing that it's based on the fact that the $C$2 cell is three digits???)

Since I want to automate this without using macros or the manual "Split text into cells" function from the Data tab up top, my original plan was to use =LEFT and =RIGHT to split the innings pitched stats into two different cells, then convert the 1/3 or 2/3 into a numerical value, and then rank the players based on a third cell with the combined value of the two cells, but I can't get past the first step of automatically splitting the digits from the fraction.

For some more info, this is how I'd like to have my auto ranking system setup:

__ A B C D E F G H I J K L
1 Rk Player IP IP __ Ranking Position __ RK Player TM IP
2 1 Sugano, Tomoyuki 102 1/3 102 __ 4 2 __ 1 Ono, Yudai 114
  • cell B2 =vlookup of players English name based on ranking from stats from pitching stats tab (all league pitching stats copied to one tab)
  • cell C2 =vlookup of inning pitched stats for player in B2
  • cell D2 =C2+.000001*ROW() for making subtle differences for excel to figure out how to rank players with the exact same stats
  • column E is a spacer
  • cell F2 =RANK(D2,$D$2:$D$16,0) (ranks player based on stat, pitching stats tab only has 15 players)
  • cell G2 =MATCH(I2,$F$2:$F$16,0) (this tells excel where the rank number in cell I2 is according to column F
  • column H is a spacer
  • cell I2 is a numerical value for ranking the top players (in this case 1st place)
  • cell J2 =INDEX($B$2:$B$16,G2) looks up the player name of the top ranked player
  • cell K2 =VLOOKUP of the reddit markdown code for the team of the player in J2 for showing flair of the team logo
  • cell L2 =INDEX($C$2:$C$16,G2) displaying the number of innings pitched for the top ranked player

I know this method seems rather drawn out, but like I said earlier, I have zero experience with macros, and the above method automatically sorts and ranks the players, meaning all I have to do is copy data from one tab with all the stats categories and reddit markdown and paste it, meaning it takes less than a minute to update a bunch of stats and rankings.

/r/NPB Thread Link - kyodonews.net