I have a form with a field called Rider on it. Unfortunately, the bosses have done most of the riders in numbers and a few with letters in it so I can't make it a numeric field. They have said they will stop using letters but there are existing contracts and who knows, they may keep doing it.
Accounting wants to sort the report numerically by rider but that obviously doesnt work.
Most of the riders are numeric. 1, 2, 7, 8.0, 8.2, 9.1 etc, however, a few of them are like 5R.
I am looking for a way to split that or convert it and sort numerically. I kind of don't even know where to start because a left or right formula wont work because they are different lengths. I was thinking maybe I could do some kind of if statement but I don't know how to say =iif(rider has a letter in it, etc
As you will see below some riders have decimals, some don't, some have letters etc.
Any ideas on how to approach this?
For reference, these are all the unique riders we have:
9.1 9 8.2 8.1 8 7.5 7.4 7.3 7.2 7.1 7 6.2 6.1 6 5R 5.2 5.1 5 4.3 4.2 4.1 4 33 32 31.1 3.4 3.3 3.2 3.1 3 24.2 23.1 23 20 2.2 2.1 2 1X 1-E 1-D 17.1 15 14.1 14 13.1 13 12.1 12 11.2 11.1 11 10.2 10.1 10 1.3 1.2 1.1 1