Skip to Content

Hi,

recently I came across a requirement that was to test if a value was in list where the list was in a variable. The variable, [list] = “123;234;456;1114678;768;999111“, I I need to test if the value of a certain dimension , [numero], was in the list [list]. As you know, the InlList operator can be used only with a literal list (that´s it a text, like “123;234;456;1114678;768;999111”, not in a variable. Then testing for [numero] InList([test]) will throw an error.

To overcome this situation, one can test for the Pos of [test] in [list], if the result is >0, [numero] is present in [list]. The downside is when [numero] is contained in another value for instance , “111” is not part of the list, but is contained in “1114678”. To parcialy overcome this issue, you can test for “;”+[numero] and [numero]+”;”. So my formula will be :

If( (Pos([list];”;”+[numero)>0)or (Pos([list];[numero]+”;”)>0);”InList”;”NotInList”)

Regards,

Rogerio

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Christian Choinkowski

    Hi Rogerio,

    not sure your formula will work in all cases. If I take your list [list] = “123;234;456;1114678;768;999111 and I search for “23” I will find it, even twice, once as “23;” and then as “;23”.

    You should check that your searched value exists with both preceeding and ending “;”, or with only ending “;” but then it must be at the begining of the list or with only preceeding “;” but then it must be at the end of your list.

    The formula would the look like:

    If( (Pos([list];”;”+[numero] +”;”)>0) or

    (Pos([list];[numero]+”;”)=1) or

    (Pos([list];”;”+[numero])=Lenght([list]) – Length([Numero])) ;”InList”;”NotInList”)

    Christian

    (0) 

Leave a Reply