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

Improve this question

1 Answer 1

Reset to default
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) )
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.