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

[aja]

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 ("").

[L-user]

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…

[aja]

Done!
Bug tracker 68844

[Paijo]

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.

[JohnSUN]

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

[dajare]

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!

[L-user]

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

[dajare]

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.

[JohnSUN]

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

[L-user]

You can use User-Defined Function by using macros, see details in: User-Defined Functions - LibreOffice Help

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.

[Paijo]

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