# [Feature Request] =word(cellref,n) function [closed]

Quite often I need to quickly extract certain words from a line of text. Text-to-columns is overkill for this because I usually have to delete all the columns that I 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 would be soooooooo 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. Invalid word position numbers should simply cause the function to return empty text ("").

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2015-11-10 10:35:19.456202

Feature request should be send to bug tracker: https://www.libreoffice.org/get-help/bug/ and described in detail what this new feature should look like.

But you need to understand that formulas in LibreOffice are not implemented just randomly but using OpenFormula standard specification. So first function must be accepted in standard then it will be implemented in LibreOffice. This can take some time...

( 2013-09-01 10:35:24 +0100 )edit
( 2013-09-02 14:25:12 +0100 )edit

You are not the only one need that kind of function. I need too. I have try search in libreoffice extension, but didn't find.

I think I want to add some funcions that might be useful for every calc user, but didn't know how to make it available as an extension. I did create an excel add-in containing some functions. Some are successfully transferred to my LO calc.

( 2013-10-02 07:24:10 +0100 )edit

Sort by » oldest newest most voted

OK, try this code:

Function getWord(BigString As String, numOfWord As Integer, Optional Separator As String) As String
Dim LocList() as String
Dim i%, cnt%
getWord = ""
If IsMissing(Separator) Then Separator = " "
LocList = Split(BigString,Separator)
If (numOfWord = 0) Or (Abs(numOfWord) > UBound(LocList())+1) Then Exit Function
cnt = 0
If numOfWord > 0 Then
For i = LBound(LocList()) To UBound(LocList())
If LocList(i) <> "" Then
cnt = cnt + 1
If cnt = numOfWord Then
getWord = LocList(i)
Exit Function
EndIf
EndIf
Next i
Else
For i = UBound(LocList()) To LBound(LocList()) Step -1
If LocList(i) <> "" Then
cnt = cnt - 1
If cnt = numOfWord Then
getWord = LocList(i)
Exit Function
EndIf
EndIf
Next i
EndIf
End Function


With negative second parameter you can get word from end of phrase - at your example

=GETWORD(A1; -3) is on

more

That's very cool - thanks! I needed @L-user's little hint ;) about how to use a custom function, but basically simples after that. Excellent stuff!

( 2013-09-01 14:12:07 +0100 )edit

In above function you can't get the last word if positive numOfWord is used or the first word if negative numOfWord is used
Sample:
A1=Good morning Vietnam
A2=GETWORD(A1;3)
and function does not return any result.

Like I see just removing line: ´ If (numOfWord = 0) Or (Abs(numOfWord) > UBound(LocList())) Then Exit Function´ and function works perfecxt

( 2013-09-01 14:39:55 +0100 )edit

Both "bug" and "fix" confirmed. I suppose that line is supposed to do a sanity check, and something is counting from 0 not 1, or something. I changed it to this: If (numOfWord = 0) Or (Abs(numOfWord) > UBound(LocList())+1) Then Exit Function and it is working correctly, but I don't know if that's a good or legal fix. It just seems to work.

( 2013-09-01 15:13:55 +0100 )edit

@David Yes, you are right! It's a very good and absolutely legal fix!

( 2013-09-01 15:56:13 +0100 )edit

Shorter and simpler macro code (work in my LO 4.0.2-or-better on Ubuntu) for word function:

Function MY_Wordf(SourceString, Posisi, Optional Delimiter As String) as String
Dim sx() As String
if Posisi <= 0 then Goto SALAH
If IsMissing(Delimiter) then Delimiter = " "
sx = Split(SourceString, Delimiter)
if Ubound(sx) < (Posisi - 1) then Goto SALAH
If Lbound(sx) >= Ubound(sx) then Goto SALAH
MY_Wordf = sx(Posisi - 1)
Exit Function

SALAH:
MY_Wordf = "#- Error: No Result"
End Function


>

more

You can use User-Defined Function by using macros, see details in: https://help.libreoffice.org/Calc/User-Defined_Functions

If you go this route, I suggest you add some prefix to your function name like: "MY_" to have function name "MY_WORD". Just in case if WORD function gets implemented by product itself and has some completely different meaning. Maybe even better, prefix function name with you name.

more