[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. Examples: a.) = CONCATENATE( A1:A5 ) is identical to: = CONCATENATE( A1;" ";A2;" ";A3;" ";A4;" ";A5 ) b.) = CONCATENATE( A1:A5; "" ) is identical to: = CONCATENATE( A1;A2;A3;A4;A5 ) c.) = CONCATENATE( A1:A2; A3:A5 ) is identical to: = CONCATENATE( A1;" ";A2;" ";A3;" ";A4;" ";A5 ) d.) = CONCATENATE( A1:A2; A3:A5; "" ) is identical to: = CONCATENATE( A1;A2;A3;A4;A5 ) RATIONALE ========== 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 = " ") result [1] "1 2 3 4 5 6 7 8 9 10" USE CASES ========= 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: =CONCATENATE(A1:E1; " ") =CONCATENATE(A2:E2; " ") Sincerely, Leonard 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]