Question about "word" formula, purpose to split one character and get key shorten wordI have one table with one column, name is "Batch ID", I want to use one script to generate one column, name is "Short ID", formula use Word( 2, :Batch ID, "L22DD1" ), only want to get 2nd word, for example, E3L22DD1K27--->K27, but how come after run JSL, its outcome is K2 not K27? kindly help to sugget whether have other solutions if it not work?
| Batch ID | Short ID |
| M3L22DD1K2 | K2 |
| E3L22DD1K45 | K45 |
| M3L22DD1K41 | K4 |
| E3L22DD1K27 | K27 |
| M3L22DD1K3 | K3 |
| M3L22DD1K42 | K42 |
| M3L22DD1K43 | K43 |
| M3L22DD1K44 | K44 |
| M3L22DD1K45 | K45 |
| M3L22DD1K4 | K4 |
| M3L22DD1K5 | K5 |
| M3L22DD1K6 | K6 |
| E3L22DD1K46 | K46 |
| E3L22DD1K47 | K47 |
| E3L22DD1K48 | K48 |
| E3L22DD1K49 | K49 |
| E3L22DD1K50 | K50 |
| E3L22DD1K51 | K51 |
| E3L22DD1K52 | K52 |
Automation and ScriptingRe: Question about "word" formula, purpose to split one character and get key shorten wordIf you know what the break string is, i.e. L22DD1, then the 2 parts can be identified and separated with the following 2 assignment statements
For the Short ID
Substr( :Batch ID, Contains( :Batch ID, "L22DD1" ) + 6 )
For the First Part of the Batch ID
Substr( :Batch ID, 1, Contains( :Batch ID, "L22DD1" ) + 5 )
Re: Question about "word" formula, purpose to split one character and get key shorten wordso thankful for help, it works well now and also understand more about function of "word".
Re: Question about "word" formula, purpose to split one character and get key shorten wordHi,
The problem with using a string as delimiter is that the function Word() does not understand it a unique string but as a collection of individual delimiters. Hence, in your example where M3L22DD1K41 yields K4, the digit "1" in your delimiter is found in the string "L22DD1".
One way to solve this is to use the Substitute function within the Word function as follows:
Word (2, substitute (:BATCH_ID, "L22DD1", "-"),"-")
This way every occurence of "L22DD1" is replace within the formula by "-" and then the desired word is extracted based on the "-" delimiter
Hope it helps.
Best
TSRe: Question about "word" formula, purpose to split one character and get key shorten wordthanks sir, it works well if the name follow same rule.
but if one row content is changed as unexpected, for example "M3L22DD1X10_I35_O55", what I want is short name "X10_I35_O55", do have better solution.
then keep use "L22DD1" separate the word to two parts and get the last one. M3L22DD1X10_I35_O55-->X10_I35_O55?
Re: Question about "word" formula, purpose to split one character and get key shorten wordHere is the formula that I would use in creating the Short ID column
New Column( "Short ID", character, formula( "K" || Word( -1, :Batch ID, "K" ) ) );
