Help with searching in string

[#1]

Hi,

I have the following task to do.

Let's say I have 3 columns: A,B,C

Each cell in Col A is in the following format: 29;30;28;27;30; ...

So just random numbers separated by ;

Each cell in Col B is in the following format: 4;5;1;3;6; ...

So these are numbers in the range of 1-25 (without repeating) in a random order, separated by ;

Col C is a single number in the range of 1-25, for example 3.

For any given row Col A and Col B have the same number of terms between the ";" (doesn't have to be the same in different rows).

And now to the task: I want to create a new column D, which contains the value from Col A found in the location of Col C in Col B.

For example, if

Col A = 29;30;28;27;30

Col B = 4;5;1;3;6

Col C = 3

Then Col D = 27

Because 3 is found in the 4th location in Col B (after the 3rd ;), and in the same location in Col A you can find 27.

I hope I explained it clearly, would appreciate any help. Please see attached file for example (I computed Col D manually for the example).

Thanks

[#2 · Accepted solution]

Should be fairly simple by using Words() and Contains().

Num(Words(:A, ";")[Contains(Words(:B, ";"), Char(:C))]);

See scripting index for more examples about those functions

[#3]

Thank you!

[#4]

Here is one way to do it

theList = Words( :B, ";" );
location = Contains( theList, Char( :C ) );
Show( theList, location );
Try( Num( Word( location, :A, ";" ) ), . );