Combining two strings

Yotam

Hi,

I have 2 columns that contain strings in the following format:

 

string1 = 1;0;2;0;0;8;0
string2 = 0;1;0;3;5;0;13

 

Note 1: Both strings contain the same number of ";" (but they are not necessarily the same length).

Note 2: The two strings have 0's in alternating positions.

Note 3: A string can contain two 0's in a row (separated by ";") as long as the other string have two non-zero terms in a row at the same positions.

I want to create a new column (string3) which is the result of merging the two columns without the 0's. For the example above:

string3 = 1;1;2;3;5;8;13

So the string3 should have the same number of ";" as string1 and string2, and must contain all the terms between the ";" by order, with no 0's.

Is there a way to do it using simple formulas only? Alternatively, using other methods such as loops?

Please see attached file.

Thanks,

Yotam 

example.jmp

txnelson

Here is a formula that will work.  I have attached a new data table with a new formula column

w = 1;
newString = "";
While( Word( w, :string1, ";" ) != "",
	If( newString != "",
		newString = newString || ","
	);
	If( Word( w, :string1, ";" ) != "0",
		newString = newString || Word( w, :string1, ";" ),
		newString = newString || Word( w, :string2, ";" )
	);
	w++;
);
newString;

The formula does not check for the situation where zeros are found in the same location on both strings, nor for the situation where non zeros might appear in the same position on both strings.  No rules were defined as to how to handle such situations.  If such issues need to be checked on, modification of the formula will be a simple matter. 

example.jmp