Concatenate columns with empty cells

[#1]

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

[#2]

You should be able to use a simple function in a new column Concat:

[#3 · Accepted solution]

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 );

[#4]

Hi, here is yet another formula.

Concat Items( Words( :Name || "," || :Age || "," || :Sex, "," ), "_" )

Cheers,

Brady