[SOLVED] Calligra sheets search substring for artist name
FedoraForum.org - Fedora Support Forums and Community
Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2018
    Location
    Vermont, USA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Calligra sheets search substring for artist name

    I am very new to Calligra sheets . Have a spreadsheet with many songs..I have chart #, year, title...but cannot figure what function will work for a way to parse out a substring from last cell -

    I want to capture just the artist name which is always preceeded by " - " and followed by " (#" and put that value in a different col. -- any suggestions appreciated..

    Sample of the 4 collumns:

    (#31) 1960 About This Thing Called Love About This Thing Called Love - Fabian (#31)
    (#33) 1960 All I Could Do Was Cry All I Could Do Was Cry - Etta James (#33)
    (#1) 1960 Alley Alley-Oop - Hollywood Argyles (#1)
    (#8) 1960 Alone At Last Alone At Last - Jackie Wilson (#8)
    (#33) 1960 Alvin's Orchestra Alvin's Orchestra - The Chipmunks & David Seville (#33)
    (#31) 1960 Am I Losing You Am I Losing You - Jim Reeves (#31)
    (#25) 1960 Am I That Easy to Forget Am I That Easy to Forget - Debbie Reynolds (#25)
    ***********************
    * Bill Clark Windham, VT *
    ***********************

  2. #2
    Join Date
    Oct 2010
    Location
    Canberra
    Posts
    3,183
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Re: Calligra sheets search substring for artist name

    I have no knowledge of Calligra, and that may be a common situation.
    If you get no response in the next few days it might be worth exporting the data as a CSV file and asking again since it should be possible using some simple text processing commands like awk or sed.

    Someone might also offer a solution to that duplication you appear to have.

    User error. Please replace user and try again

  3. #3
    Join Date
    Feb 2005
    Location
    London, UK
    Posts
    1,137
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    I hate Calligra, every time I go to use it something annoys me about it. Figuring this out was no exception. For example, try creating a string in cell A1 that contains an open bracket, e.g "test ( brackets". Then in cell B2 create the formula =find(A1; " ( ") and press enter, for some reason it doesn't realise that the ( is in the quotes and will auto-append a non-matching ) at the end of the formula and then throw a parse error as there are too many brackets - that's just dumb! There must be some way to escape the ( but \ doesn't work so god knows.

    Anyway, to answer your question while working around the above issue using a "?" wildcard instead of "(", this works:

    Code:
    =mid(a1;find(" - ";a1)+3;search(" ?#";a1)-find(" - ";a1)-3)

  4. #4
    Join Date
    Mar 2018
    Location
    Vermont, USA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    Thank you HaydnH for the code - I will try this in that mysterious app called Calligra, and will report back//
    ***********************
    * Bill Clark Windham, VT *
    ***********************

  5. #5
    Join Date
    Mar 2018
    Location
    Vermont, USA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    So I played around with the code -- trying to wrap my head around logic and syntax.

    assume that cell G11 contains text About This Thing Called Love - Fabian (#31) . There are 3 items of information here (Title, Artist, Chart #).

    using this code =mid(g11;find(" - ";g11)+3) the result is Fabian (#31)


    We are almost there ! .. Now I need to isolate Fabian, whose name is 2 blank spaces before the open (

    It is here that I am stuck -- I cannot find a left$ function or anything like that. I am obviously missing some obvious logic ! Many thx for your guidance.
    ***********************
    * Bill Clark Windham, VT *
    ***********************

  6. #6
    Join Date
    Feb 2005
    Location
    London, UK
    Posts
    1,137
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    You missed part of the code, the 3rd argument (see the green bit here). The mid function takes 3 arguments, 1) the cell containing the text to create a substring from, 2) the start position of the substring and 3) the length of the substring.

    Code:
    =mid(a1; find(" - ";a1)+3; search(" ?#";a1)-find(" - ";a1)-3)

    The second argument, find(" - ";a1)+3, says we want the substring to start at the point of " - ", plus 3 as there are 3 characters in the search string (" - ") itself.

    The 3rd argument, search(" ?#";a1)-find(" - ";a1)-3, says we want the length of the substring to be the character where we find " ?#" (? is the wild card for any character) minus the length of the string before the " - " and minus 3 again to remove the " ?#" part.

  7. #7
    Join Date
    Feb 2005
    Location
    London, UK
    Posts
    1,137
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    On a side note, Libreoffice doesn't have the annoying extra bracket issue, so if you used Libre you could use the ( in the find:

    Code:
    =MID(A1, FIND(" - ", A1)+3, FIND(" (#", A1)-FIND( " - ", A1)-3)

  8. #8
    Join Date
    Mar 2018
    Location
    Vermont, USA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    Thank you for the mensa-level explanation -- I do understand the syntax and logic of your expression..

    Regretfully, I cannot actually see any results on the spreadsheet. I get no errors - just nothing showing in the cell.. this is too wierd...do I need to format the cells as text ?

    I started a new sheet ...

    I put Burning Bridges - Jack Scott (#3) in cell A1

    then I pasted your code in cell A7 ..result is no errors, just an empty cell the formula is displayed above when I click on the cell ...... even tried capitalizing the a in the cell reference..

    FWIW - .I am running version 3.1.0 of Calligra sheets..I am going to figure this out, as this makes no sense that it works for you but not for me
    ***********************
    * Bill Clark Windham, VT *
    ***********************

  9. #9
    Join Date
    Mar 2018
    Location
    Vermont, USA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    FWIW, I dl Libre office, and loaded a new spreadsheet in LibreOffice Calc

    I put Burning Bridges - Jack Scott (#3) in cell A1

    Then in another cell, I put =MID(A1, FIND(" - ", A1)+3, 12) and I got the result Jack Scott - I just substituted 12 for the third part of your expression ...
    ***********************
    * Bill Clark Windham, VT *
    ***********************

  10. #10
    Join Date
    Feb 2005
    Location
    London, UK
    Posts
    1,137
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    My advice would be to break the formula down. So in cell A1 you'd have the full string, in B1 put argument 2 (=find(" - ";a1)+3), in cell C1 put argument 3 (=search(" ?#";a1)-find(" - ";a1)-3) and then in cell D1 put "=mid(a1, b1, c1)". That way you can see the numbers in the arguments and see where you're going wrong.

  11. #11
    Join Date
    Mar 2018
    Location
    Vermont, USA
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    **** Your guidance paid off -- the formula for the second argument I am using is =FIND("#", A1)-FIND( " - ",A1)-7 and I appear consistently get the correct artist listed in the last column ... I have been able to reproduce correct results for a variety of titles --- excellent troubleshooting tip.. !
    ***********************
    * Bill Clark Windham, VT *
    ***********************

  12. #12
    Join Date
    Feb 2005
    Location
    London, UK
    Posts
    1,137
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    Depending what you want to do with the resulting column full of artist names, you may want to convert them from a formula to simple text (i.e: if you click an artist cell now the formula bar will still say =mid( blah blah. The quickest way to do that is to select the column by clicking it's letter in the header row, "ctrl+c" to copy and then "ctrl+shift+v" quickly followed by enter to accept the paste special pop up.

    p.s: Can I mark this as solved now?

  13. #13
    Join Date
    Jul 2004
    Location
    Colton, NY; Junction of Heaven & Earth (also Routes 56 & 68).
    Age
    74
    Posts
    23,827
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Calligra sheets search substring for artist name

    Linux & Beer - That TOTALLY Computes!
    Registered Linux User #362651


    Don't use any of my solutions on working computers or near small children.

Similar Threads

  1. [SOLVED]
    bash script and getting a substring or character
    By rholme in forum Using Fedora
    Replies: 2
    Last Post: 27th February 2019, 05:52 PM
  2. calligra-krita shortcuts not working
    By guntermesrh in forum Media software
    Replies: 0
    Last Post: 18th August 2015, 06:58 PM
  3. Calligra sheets --- crash on close file!
    By deshmukh in forum Using Fedora
    Replies: 0
    Last Post: 27th September 2014, 06:50 AM
  4. substring in shell
    By mystical dervis in forum Programming & Packaging
    Replies: 10
    Last Post: 1st July 2007, 11:28 AM
  5. what should I run? (3d artist)
    By Proffesso in forum Using Fedora
    Replies: 1
    Last Post: 19th April 2005, 12:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •