How to set up a program that converts text strings to specific numerical values

[emarsh5050]

I want to do some data analysis on my rock climbing statistics using excel but am new to it. What I want to do is convert text strings of grades on rock climbs to numerical values that I can specify according to a non-linear point system I have created. For example:

Point System			Example
Grade	Points		Date	Grade	Points
5.7≤	1​		17-Jul​	10a	4​
5.8	2​			8	2​
5.9	3​			11a	13​
5.10a	4​			10a	4​
5.10b	6​
5.10c	8​
5.10d	10​
5.11a	13​
5.11b	16​
5.11c	19​
5.11d	21​
5.12a	25​
5.12b	29​
5.12c	23​
5.12d	27​
5.13a	32​

Thanks in advance!

 

[Z51]

Let's assume:
The Points System table is in columns A:B, and that your Example table (Date, Grade and Points) are in columns D, E and F. In column F you can try:

=VLOOKUP("5."&E2,A:B,2,0)

Perhaps leave off the ≤ symbol in the first row, otherwise you'll have to account for that in the formula.

You could also use a straight LOOKUP formula using arrays (both arrays should be sorted an ascending order - including text) like so:

=LOOKUP("5."&E2,{"0","5.10a","5.10b","5.10c","5.10d","5.11a","5.11b","5.11c","5.11d","5.12a","5.12b","5.12c","5.12d","5.13a","5.2","5.8","5.9"},{1,4,6,8,10,13,16,19,21,25,29,23,27,32,1,2,3})