0

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.