Board Thread:General Discussion/@comment-27006659-20160418175659/@comment-27006659-20160503223108

Splitting the Laps from PR is quite the challenge. So far, I can only do it with a formula in helper columns. I'd much rather take care of it in Power Query, but I'm not that smart (yet).

This one splits out the laps

=IFERROR(IF([@[Laps/PR]]>10000,LEFT([@[Laps/PR]],2)*1,IF([@[Laps/PR]]>1000,LEFT([@[Laps/PR]],1)*1,IF([@[Laps/PR]]<1000,LEFT([@[Laps/PR]],1)*1))),"")

And this one does the PR

=IF([@[Laps/PR]]>10000,RIGHT([@[Laps/PR]],3)*1,IF([@[Laps/PR]]>1000,RIGHT([@[Laps/PR]],3)*1,IF([@[Laps/PR]]>100,RIGHT([@[Laps/PR]],2)*1,"")))

These formulas work for everyting except where there are 10 or more laps with a PR of less then 100. Do you know if there are any such events?

Another challenge is that many of the Tier and Location fields have words which run together because Power Query doesn't see, or doesn't interpret the line breaks which are present in the tables. (Why the hell are there so many manual line breaks in all the tables any way?????). I got some help from the great people on the Chandoo forum, that takes care of most of those instances. For the odd ones where it doesn't work, like Ferrari 412 T2 Showcase you can do a Replace Value in Power query.

Chandoo Thread

http://chandoo.org/forum/threads/insert-space-before-uppercase-except-if-preceded-by-space-or-uppercase.29014/

Take a fresh download of my RR3_Project file.

https://www.dropbox.com/s/gmrt2z9u3jgtvzk/RR3_Project.xlsm?dl=0

Look at the EventTemplate tab, which is currently populated with the http://rr3.wikia.com/wiki/Ferrari_Evolutiondata and see if any of it helps. C