Count the position of a string in another string that is seperated by comma

[#1]

Hi Everyone,

I have a string that is seperated by comma like this:

41609,41640,41671,41699,41730,41760,41791,41821,41852,41883,41913,41944,41974,42005,42036

I want to count the position of 42036 in the above string.

The result I would like to have is 15.

I have tried index('41609,41640,41671,41699,41730,41760,41791,41821,41852,41883,41913,41944,41974,42005,42036','42036'), and it gives me 85.

Thanks very much for your help.

Kind regards

[SergeyMak]

Hi,

this script gives you 15

=match(42036,41609,41640,41671,41699,41730,41760,41791,41821,41852,41883,41913,41944,41974,42005,42036)

Regards,

Sergey

[SergeyMak · Accepted solution]

Or another option

=Ceil(index('41609,41640,41671,41699,41730,41760,41791,41821,41852,41883,41913,41944,41974,42005,42036','42036')/6)

[#4]

Thanks very much for your help, Sergey.

Unfortunately, it does not work in my case because the long string is constructed using expressions.

My situation is more like this:

=match(42036,'41609,41640,41671,41699,41730,41760,41791,41821,41852,41883,41913,41944,41974,42005,42036')

where the long string in the apostrophe is constructed using expressions.

And this returns 0.

[SergeyMak]

did you try the second option?

=Ceil(index('41609,41640,41671,41699,41730,41760,41791,41821,41852,41883,41913,41944,41974,42005,42036','42036')/6)

Regards,

Sergey

[#6]

Thanks for the help again, Sergey.

My long string is constructed using expression like this:

=concat({<Date=, [Month Year]=, [Supply Mark]=, SValue={">0"}>}aggr(num(only({<Date=, [Month Year]=, [Supply Mark]=, SValue={">0"}>}[Month Year])),[Month Year]),',')

So the number of dates varies based on my selections in other fields.

[SergeyMak]

yes. That's why I divided it by 6 and rounded to upper number.

Looks like should work.

Can you share the example of your app, because it's hard to say without your data

Regards,

Sergey

[#8]

Yes, you are right. that works.

I thought that if the number of dates varies, you have to divided different numbers, but it seems that I don'e need to do so.

Thanks again for your help!!!!