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 |
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.