Complex calculated column in SharePoint

Question

we have to create a calculated column from a string that can be of the form A01 (letter + 2 digit number) or it can be 2 letters plus number, as in AA1

the logis to use the ASCII codes of the letters like this

first, check if the middle character is a String or is Numeric (as in some cases a T-Code can have A, B or C as the second character instead of 1-9)
________________________________________
If it’s a number, we take the Ascii Code of the first character and concatenate it with the 2 numbers at the end of the T-Code.

EG: P17
Ascii Code of P is 80
The Till Code for P17 is 8017
________________________________________
If the middle character is a letter then we do this:

take the Ascii code of the first character -30
take the Ascii code of the second character -65
take the last number
Concatenate them in that order

EG: BC1
Ascii Code of B is 66 – 30 = 36
Ascii Code of C is 67 – 65 = 2
Last Number is 1
The Till Code for BC1 is 3621

is it possible to do something like that in a calculated field?

Or would we need several calculated fields to do it?

There is no problem using severl fields or even a lookup reference column with all the ASCII codes in it...

Answer 1

here is the formula we came up with that seems to work fine:

=IF(
    FIND(UPPER(MID([T-Code],2,1)),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ",1)<=10
    ,CODE(UPPER(MID([T-Code],1,1))) & MID([T-Code],2,1)&MID([T-Code],3,1)
    ,CODE(MID([T-Code],1,1))-30&CODE(MID([T-Code],2,1))-65&MID([T-Code],3,1)
)