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

asked 2013-09-01 07:53:01 +0100

aja
1 ●1 ●1 ●1

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 flag offensive 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

Comments

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...

L-user ( 2013-09-01 10:35:24 +0100 )edit

Done! Bug tracker 68844

aja ( 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.

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

add a comment

4

answered 2013-09-01 12:26:23 +0100

JohnSUN

7937 ●10 ●44 ●85

updated 2013-09-01 15:51:30 +0100

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

edit flag offensive delete link

Comments

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!

David ( 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

L-user ( 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.

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

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

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

add a comment

0

answered 2013-09-01 10:33:46 +0100

L-user
2877 ●33 ●45 ●64

updated 2013-09-01 10:42:42 +0100

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.

edit flag offensive delete link

add a comment

0

answered 2013-10-03 06:54:11 +0100

Paijo
263 ●20 ●21 ●27

updated 2013-10-03 06:55:44 +0100

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

>

edit flag offensive delete link

add a comment

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

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

Comments

3 Answers

Comments

Question Tools

Stats

Related questions

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

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

Comments

3 Answers

Comments

Question Tools

Stats

Related questions

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

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