Enhancements to Calc functions

classic Classic list List threaded Threaded
2 messages Options
David Lynch David Lynch
Reply | Threaded
Open this post in threaded view
|

Enhancements to Calc functions

The new functions, TEXTJOIN and CONCAT, have been a great help, allowing
me to simplify some spreadsheets.

Partly motivated by this success, I have asked for enhancements to some
existing Calc functions that I would find useful:
<https://bugs.documentfoundation.org/show_bug.cgi?id=107369>
<https://bugs.documentfoundation.org/show_bug.cgi?id=107370>
<https://bugs.documentfoundation.org/show_bug.cgi?id=107371>

Comments (and support) would be welcome.

All these enhancements are such that existing spreadsheets would
continue to work without needing modification.

The enhancements are highlighted below (*** ... ***).

SUBSTITUTE("Text"; "SearchText"; "NewText"; Occurrence)

     Text is the text string in which text segments are to be exchanged.
     SearchText is the text segment that is to be replaced (a number of
times).
     NewText is the text string that is to replace the text segment.
     Occurrence (optional) indicates which occurrence of the search text
is to be replaced. If this parameter is omitted the search text is
replaced throughout.
*** If SearchText and NewText are both arrays, they must be the same
length: then each array element of NewText replaces the corresponding
element of SearchText. If only SearchText is an array, NewText replaces
each element of SearchText. ***

REPLACE("Text"; Position; Length; "NewText")
     Text refers to text of which a part will be replaced.
     Position refers to the position within the text where the
replacement will begin.
     Length is the number of characters in Text to be replaced.
     NewText refers to the text which replaces Text.
*** Text and NewText can be regular expressions. ***

MATCH(SearchCriterion; LookupArray; Type)
     SearchCriterion is the value which is to be searched for in the
single-row or single-column array.
     LookupArray is the reference searched. A lookup array can be a
single row or column, or part of a single row or column.
     Type may take the values 1, 0, or -1. If Type = 1 or if this
optional parameter is omitted, it is assumed that the first column of
the search array is sorted in ascending order. If Type = -1 it is
assumed that the column is sorted in descending order. This corresponds
to the same function in Microsoft Excel.
     If Type = 0, only exact matches are found. If the search criterion
is found more than once, the function returns the index of the first
matching value. Only if Type = 0 *** or Type > 1 *** can you search for
regular expressions (if enabled in calculation options) or wildcards (if
enabled in calculation options).
     If Type = 1 or the third parameter is omitted, the index of the
last value that is smaller than or equal to the search criterion is
returned. This applies even when the search array is not sorted. For
Type = -1, the index of the first value that is larger than or equal is
returned.
*** If Type > 1, only exact matches are found. If the search criterion
is found more than once, the function returns the index of the Type'th
matching value.

VLOOKUP(SearchCriterion; Array; Index; Sorted)
HLOOKUP(SearchCriterion; Array; Index; Sorted)
*** Sorted does the same as the Type parameter in function MATCH. ***


--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
jonathon-6 jonathon-6
Reply | Threaded
Open this post in threaded view
|

Re: Enhancements to Calc functions

On 04/23/2017 02:12 PM, David Lynch wrote:

> SUBSTITUTE("Text"; "SearchText"; "NewText"; Occurrence)
> REPLACE("Text"; Position; Length; "NewText")

What is the use case for those functions?

> MATCH(SearchCriterion; LookupArray; Type)

Why use this, rather than VLookUp or HLookUp?

jonathon

--
To unsubscribe e-mail to: [hidden email]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted