topic Re: Question about "word" formula, purpose to split one character and get key shorten word in Discussions

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

qspringleaf — Fri, 09 Jun 2023 23:41:02 GMT

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

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

txnelson — Thu, 22 Oct 2020 02:44:46 GMT

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

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

qspringleaf — Thu, 22 Oct 2020 03:48:37 GMT

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?

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

Thierry_S — Thu, 22 Oct 2020 05:08:03 GMT

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

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

qspringleaf — Thu, 22 Oct 2020 05:15:47 GMT

so 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 word

txnelson — Thu, 22 Oct 2020 09:16:32 GMT

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 )