Concatenate columns with empty cells

JMP_fan

Hi all,

I have a dataset with different columns that have some empty random cells (like the example in the table below). I would like to concatenate the values of the different columns and use the "_" to separate the content of the cells only if the content of the cells is different from empty value (as in column "concat" below). Does anyone have a suggestion on a how to to this operation in a smart way?

Thanks for your help! 

 

Name	Age	Sex	Concat
Mads	31		Mads_31
		M	M
John	25		John_25
Julia	19	F	Julia_19_F

Example.jmp

txnelson

Here is one way of writing the formula

colList = {"Column 1", "Column 2", "Column 3"};
x = "";
For( i = 1, i <= N Items( colList ), i++,
	If( As Column( colList[i] ) != "",
		If( x != "", x = x || "_" );
		x = x || Char( As Column( colList[i] ) );
	)
);
Trim( x );