Bug 68844 - Other: [Feature Request] =word(cellref,n,[delimiter]) function to extract the nth word
Summary: Other: [Feature Request] =word(cellref,n,[delimiter]) function to extract the...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2013-09-02 12:21 UTC by androidarmstrong
Modified: 2020-08-31 13:13 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description androidarmstrong 2013-09-02 12:21:03 UTC
Problem description:

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.

Current behavior:
Limited string manipulation functions which are less easy and more time-consuming to use in the real world.

Expected behavior:
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: release
Comment 1 Paijo 2013-10-02 05:05:11 UTC
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.

Importance: Enhancement
Status: New