OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office-comment message

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

Subject: Expand CONCATENATE() to Ranges

Dear TC-members,

This is a proposal to extend the CONCATENATE() function to cover 
spreadsheet ranges.

Current definition:
   CONCATENATE( { Text T }+ )

Suggested Extension:
   CONCATENATE( { Range }+, separator = " " )

The last non-range parameter, IF specified, is the separator used to 
concatenate the strings. The default is the space character (" "). IF 
the last parameter is a range, then NO separator was specified, and the 
default will be used.

is identical to:
 = CONCATENATE( A1;" ";A2;" ";A3;" ";A4;" ";A5 )

  = CONCATENATE( A1:A5; "" )
is identical to:
 = CONCATENATE( A1;A2;A3;A4;A5 )

  = CONCATENATE( A1:A2; A3:A5 )
is identical to:
 = CONCATENATE( A1;" ";A2;" ";A3;" ";A4;" ";A5 )

  = CONCATENATE( A1:A2; A3:A5; "" )
is identical to:
 = CONCATENATE( A1;A2;A3;A4;A5 )

It is currently cumbersome to concatenate various strings, as the 
existing functionality does NOT provide a convenient way to specify the 
separator and neither to concatenate arrays (ranges).

Various programming languages do offer such functionality:

  Perl: join(JOINER, @LIST);
  PHP: join(",", $array)
  R: paste(vector, collapse = " ")

e.g. in R:
  our.vector <- 1:10  # NUMBERS  FROM  1 to 10
  result <- paste(x, collapse = " ")
[1] "1 2 3 4 5 6 7 8 9 10"

Convenient concatenation of strings in string/text-centered spreadsheets.
More legible and maintainable code:
Instead of:
 =CONCATENATE(A1;" ";B1;" ";C1;" ";...)
 =CONCATENATE(A2;" ";B2;" ";C2;" ";...)
The user will have:



P.S. Because the existing CONCATENATE() does NOT provide a separator, 
changing the *separator* to be the first parameter (as in Perl/PHP) is 
NOT backwards compatible and will introduce some riots. Therefore, I 
propose to let *separator* be the last parameter that is NOT a range in 
a function call with ranges. Using CONCATENATE() without ranges will 
therefore lack a *separator*. The *separator* is only defined for the 
function having one of its arguments a range. R does have a much more 
clear way to specify parameters, but this is unfortunately not available 
in ODF. Limiting the CONCATENATE to only *one range* (i.e. CONCATENATE( 
range, separator) instead of CONCATENATE( range+; separator) ), is also 
counterproductive as it removes much of the gained flexibility.

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]