I 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 IDShort IDM3L22DD1K2K2E3L22DD1K45K45M3L22DD1K41K4E3L22DD1K27K27M3L22DD1K3K3M3L22DD1K42K42M3L22DD1K43K43M3L22DD1K44K44M3L22DD1K45K45M3L22DD1K4K4M3L22DD1K5K5M3L22DD1K6K6E3L22DD1K46K46E3L22DD1K47K47E3L22DD1K48K48E3L22DD1K49K49E3L22DD1K50K50E3L22DD1K51K51E3L22DD1K52K52

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Question about "word" formula, purpose to split one character and get key shorten word

qspringleaf

I 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

Accepted answers

Thierry_S

Hi,
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
TS

All comments

txnelson

Here 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" )&nbsp;)&nbsp;);

qspringleaf

thanks 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?

Thierry_S

qspringleaf

so thankful for help, it works well now and also understand more about function of "word".

txnelson

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