CONCAT formula

[veb86]

Hi.
I really need to use the CONCAT formula. Please help.
In older versions of EXCEL, it's called CONCATENATE; in newer versions, it's called _xlfn.CONCAT.
Their behavior is almost identical; they've been renamed, and the ability to concatenate ranges has been added.
For backward compatibility, it's best if they both point to the same function.
Here's a link to the instructions:
https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

[veb86]

I have Excel 2019, and the CONATENATE formula doesn't work. Only the CONCAT formula works.

[wp]

I have Excel 2019, and the CONATENATE formula doesn't work. Only the CONCAT formula works.

Strange. MS writes in the document that you are citing: "However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel."  So, this seems to be wrong?

What does not work? Excel 2019 does not accept the name "CONCATENATE"? Or do you need the new functionality of the CONCAT formula?

How does CONCAT appear in the internal xml file? Can you send me a simple xlsx file written by Excel 2019 which contains a CONCAT formula? (I myself only have Excel 2016, and I am rather sure that this will be the last Excel version for me).

[veb86]

I also read the link, but in the new version of EXCEL, CONCATENATE behaves differently than described. It displays with an exclamation mark.
I really need to concatenate strings in a given array.

It's written to the file like this: _xlfn.CONCAT
Cell A3

[wp]

Since I cannot test the CONCAT function in my Excel version (2016), could you give me a description what fpspreadsheet is supposed to do here?

Your sample file contains the formula CONCAT(A1:C1). I assume that you want to concatenate the three strings in cells A1, B1 and C1, this is rather clear. And I assume also, that the same syntax can be applied along the column, e.g. CONCAT(A1:A3). But what if none of the range dimensions is 1, ie. CONCAT(A1:C3)? What is the order in which the cells are picked? Rows first, or columns first? Or is this case forbidden?

And I guess, the behaviour of the old CONCATENATE should be retained?

[veb86]

Let's leave CONCATENATE as is. CONCATENATE is a more universal formula anyway.

I assume that you want to concatenate the three strings in cells A1, B1 and C1, this is rather clear.

Yes, that's right.

And I assume also, that the same syntax can be applied along the column, e.g. CONCAT(A1:A3)

Yes, that's right.

But what if none of the range dimensions is 1, ie. CONCAT(A1:C3)? What is the order in which the cells are picked? Rows first, or columns first? Or is this case forbidden?

This case is allowed
link:
https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2
Example 1, Example 2, Example 3 - the examples explain it in detail.

PS
Right now, I really need the A1:An array to work.

[wp]

Committed to SVN a new version which supports the function CONCAT with range support. Range was restricted to a single sheet because I don't know how the strings of multiple sheets in a 3d-reference would be combined. (CONCAT(Sheet1

One more question: How do you enter this formula in the worksheet? Just as "CONCAT(...)"? Or as "_xlfn.CONCAT(...)"? In the committed version, the formula must be entered with the "_xlfn" prefix (which, I think, is ugly and confusing...)

Find in the attachment a demo project which writes both xlsx and ods files. But OpenDocument Calc is not able to understand this formula...

I am not sure whether I keep the formula as it is now. Maybe I should split these very-advanced Excel-only formulas off into a separate unit, and users requiring them should "use" this unit while the normal users will not be bothered and confused by them?

[wp]

Now I also added the TEXTJOIN formula which does a similar job, but is also available in LibreOffice Calc. There are just two additional parameters at the beginning, one for the delimiter between the joined texts, and one indicating whether empty cells should be skipped

[veb86]

One more question: How do you enter this formula in the worksheet? Just as "CONCAT(...)"? Or as "_xlfn.CONCAT(...)"? In the committed version, the formula must be entered with the "_xlfn" prefix (which, I think, is ugly and confusing...)

CONCAT(...)

Or as "_xlfn.CONCAT(...)"? In the committed version, the formula must be entered with the "_xlfn" prefix (which, I think, is ugly and confusing...)

Only CONCAT(...)

I am not sure whether I keep the formula as it is now. Maybe I should split these very-advanced Excel-only formulas off into a separate unit, and users requiring them should "use" this unit while the normal users will not be bothered and confused by them?

I like LibreCalc, but I'm doing calculations for users with low computer skills. It's hard for them to learn anything new. I think it's better to leave it exclusively for Excel.

Now I also added the TEXTJOIN formula which does a similar job, but is also available in LibreOffice Calc. There are just two additional parameters at the beginning, one for the delimiter between the joined texts, and one indicating whether empty cells should be skipped

TEXTJOIN is much more interesting than CONCAT.
TEXTJOIN is more advanced and convenient.

Thank you very much. I'll be testing it today.

[Zvoni]

Haven't looked into the source-code, but since i had to use TEXTJOIN last week:
Be aware, that TEXTJOIN itself also accepts an Array as third parameter
i had to use TEXTJOIN in conjunction with SEQUENCE.

I had a string of Hex-Values, which i had to converto to its Ascii-representation.
so i had to throw the string at the SEQUENCE-Function, to read out the Hex-Values "pair-wise", convert the Hex-Pairs to Decimal, convert the Decimal to Character.
and the resulting Array of Characters i had to throw back into TEXTJOIN.

Just as a FYI

[wp]

Extended the formula engine by a "Prefix" element ('_xlfn.') which is put before a function name when an Excel or Calc file is written to file. The prefix is ignored when a formula is read from file or entered in a worksheet. The necessity of writing a prefix is specified by a new optional parameter in the formula definition by "AddFunction" (unit fpsFunc):

Code: Pascal  [Select][+][-]

1.     AddFunction(bcStrings, 'CONCAT', 'S', 'S+',INT_EXCEL_SHEET_FUNC_UNKNOWN, @fpsCONCAT, EXCEL_PREFIX);

Since the prefix is ignored, it is no longer necessary to type '_xlfn.', but typing it is not harmful for fpSpreadsheet:

Code: Pascal  [Select][+][-]

1.     sheet.WriteFormula(0, 2, 'CONCAT(A1:A3)');                    // no prefix
2.     sheet.WriteFormula(1, 2, '_xlfn.CONCAT(A1:B1)');              // using the EXCEL_PREFIX
3.     sheet.WriteFormula(2, 2, 'COM.MICROSOFT.CONCAT(A1:B3)');      // using the ODS_PREFIX, but fps will write the EXCEL_PREFIX to the files

Note that there is also an ODS_PREFIX which LibreOfficeCalc writes to its own files, but ODS can also work with the EXCEL_PREFIX ('_xlfn.'). It is just introduced to allow reading of original ODS files which may contain it.  fpSpreadsheet always writes the _xlfn prefix even to ODS files for simplicty (it is accepted as such by Calc).