concatenation problem!

[#1]

hi to everyone!

I would like to obtein the following result from a concat function:

FIELDCONCATENATED ----> 'field(i)','field(i+1)','field(i+2)',...,'field(n)'

ex:

'1/1/8','2/6/90','4/14/45'

i'm not able to concatenate the:   '

I need this solution, because i'm tring to update in a dinamic way the result of a MATCH function, without changing the script (but only a variable in an input box and adding a line in an excel document)

FOR THE MOMENT MY MATCH CONDITION IS THE FOLLOWING:

IF(MATCH(fieldP,

                         'P6/0/0',

                                                  'P5/0/0',

                         ETC....,

                                                  '25/2.5/0I'),

'fieldP is present','change'))))

AND I WOULD LIKE THIS:

IF(MATCH(fieldP,FIELDCONCATENATED),

'fieldP is present','change'))))

Therefore if it will be necessary to add a new fieldP in the excel, I don't need to open the script!

I wanted to explain this becase if you have another way of thinking should be great!

Thank You !

[Gysbert_Wassenaar · Accepted solution]

Use chr(39): concat(distinct chr(39) & MyField & chr(39) ,',')

[#3]

😄

[#4]

hi Gysbert,

thanks because your solution for the concatenation is perfect, but the field resulting from the concatenation is not being evaluated from the match:

i mean:

if concat(distinct chr(39) & MyField & chr(39) ,',') as list

and list='a','b'

match(fieldP, 'a','b') <>0

but

matct(fieldP,list)=0

[Gysbert_Wassenaar]

if list is a variable you need to dollar-expand it: match(fieldP, $(list))

[#6]

unfortunatly is a field

[Gysbert_Wassenaar]

Then try substringcount(fieldP, list) or perhaps substringcount(chr(39)&fieldP&chr(39), list) to prevent some possible and unintended matches.

[#8]

hi,

the last solution is quite good, but still not perfect, because:

substringcount('5/5/0a', '5/5/0') = substringcount('5/5/0', '5/5/0') = 1

[Gysbert_Wassenaar]

Oops, the search string should be the second argument: substringcount(list, chr(39)&fieldP&chr(39))

[#10]

in italian we could say: GRANDISSIMO!

GRAZIE GRAZIE!