Formulas in grid cells, problem with commas

[Stephen Trinder]

Hi all.

I have a formula in a cell that looks like this:

=D2 & IF(D2="", "", ", ") & D3 & IF(D3="", "", ", ")

The formula works fine in Excel, but doesn''t seem to work in the Syncfusion grid. I get a "requires 3 arguments" error in the cell.
The problem seems to be the third argument of the If test, where I am trying to insert ", ".

Looks like the comma is confusing the grid, as it thinks there is another argument.

Have I just made a syntax error? Is there any other way I can do this?

Thanks,
-Stephen Trinder

[Administrator]

The comma inside the quotes is causing a problem.

Until we get this fixed, one way to work around it is to add a custom function to conditionally add the comma to a non-empty string. Here is a little sample.

http://www.syncfusion.com/Support/user/uploads/GC_Formula_963588f8.zip

[Stephen Trinder]

Great, thanks Clay. That''ll get me going for now.

Another quick question, is there any way to remove the " at the beginning and end of the formula result string?

I would like the cell to contain
abc, xyz
instead of
"abc, xyz"

Is there any way to achieve that? Or is it just a function of the grid?

Thanks,
-Stephen

[Administrator]

Probably the easiest way to handle this is to write a formula that accepts multiple arguments and returns a comma delimited string from the arguments in teh format that you desire.

//Usage:   string s = "=CatComma(D2,D3,D4)";
//Usage:   string s = "=CatComma(A1:A5)";
private string ComputeCatComma(string s)
{
	string[] args = this.engine.GetCellsFromArgs(s);
	string result = "";
	foreach(string arg in args)
	{
		string s1 = this.engine.GetValueFromArg(arg);
		if(s1.Length > 0 && result.Length > 0)
			result += ", ";
		result += s1;
	}
	if(result.Length == 0)
		result = " ";// avoids a zero
	return result;
}