Originally asked on http://ask.libreoffice.org/en/question/22070/feature-request-wordcellrefn-function/
Quite often I need to quickly extract certain words from lines of text. Text-to-columns is overkill for this because I usually have to delete all the columns that I'm not interested in.
What I'd like to see is a function called "word" which extracts the nth word from the text in a cell, which can be used like this:
A B C
the cat sat on the mat =word(A1,2) =word(A1,6)
The result is:
the cat sat on the mat cat mat
This is so much easier than hacking about with =mid(A1,1,find(" ",A1)) ...and similar acrobatics. A "word" is space-delimited text by default, and leading/trailing/multiple spaces should be insignificant. That is, when you ask for word 1, you should get the first space-delimited word and no surrounding spaces.
The full function could allow a delimiter to be specified, for example =word(cellref,pos[,delimiter]) in which you could specify one or more delimiters. The delimiter, if omitted, should default to a space.
Invalid word position numbers should simply cause the function to return empty text ("").
A negative word position number should return the nth last word in the string. For example, word("the cat",-1) should return "cat".
Specifying zero as the word position should return empty text ("").
The function should never return an error. The result is not used for calculation, so an error indication is unnecessary. Empty text is an acceptable return value when an error occurs.
The function should be called "word". A precedent for this is the almost identical function built-in to the Rexx Programming Language - see http://www.rexxinfo.org/html/functions.html#word.
Steps to reproduce:
None. This is a feature request.
Limited string manipulation functions which are less easy and more time-consuming to use in the real world.
Intuitive easy-to-use built-in text manipulation functions which actually use the power of computers rather than requiring an end-user to be a programmer. A programmer could easily implement this as a
a user-defined function, but the vast majority of users are not programmers and they would benefit from simple built-in functions such as this one. I'm not asking for a regular expression parser. The simple functions should be built-in.
Operating System: All
Version: 22.214.171.124 release
I do need this kind of function in spreadsheet as well.
Fortunately, I can create a macro.
But it should be better if there is a built-in function. Because not all user can create such macro.
I wish LO developer can offer better things, such the delimiter can be in any length, not just a single character, etc.