Announcement

Collapse
No announcement yet.

LibreOffice Calc: how to test for presence of text string in cell

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    LibreOffice Calc: how to test for presence of text string in cell

    I'm a bit surprised to be asking this, as I've worked with spreadsheets a long time....but, I'm stuck on this one.

    I need to test for a substring ("-flac") in the string contents of a cell, and return a value if found, and another value if not. It's the latter alternative that's giving me fits. I cannot find a way to return anything other than a "#VALUE" when the substring is NOT present, and that brings my larger function to a halt.

    Here's the function I'm trying to use:
    Code:
    =IF(SEARCH("-flac",C15),"y","")
    . If the target cell contains, say, "xxxxx -flac", I get a "y" result, but if not I get "#VALUE!" as output.

    Use of the FIND function in place of SEARCH yields the same result. I can find no way around this problem, and Google search for solutions yielded no usable results.

    Any suggestions? Ideas? Any would be most appreciated. There surely is a solution to this problem...

    Thanks!
    Last edited by Snowhog; May 11, 2013, 09:09 PM.

    #2
    Re: LibreOffice Calc: how to test for presence of text string in cell

    perhaps a little more explanation with specific information might help folks to come up with a solution.

    I entered "aflac" and "aboyd" into each of two cells, I then used find and replace and in the find box I entered the letters "flac" and in the replace box I entered the letters "insurance" when I did the search libreoffice highlighted the box with the letters "aflac" and I hit replace and got "ainsurance"

    So could you explain what you mean, in particular, by the part of your question "return a value"

    From the statement I might assume you mean that if the spreadsheet finds a word like "aflac" it will print out someplace the number 5 and if it instead finds "aboyd" it will print out someplace the number 7?

    And as an extension anothe function would add up all the 7s and also add up all the 5s?

    woodsmoke

    Comment


      #3
      Re: LibreOffice Calc: how to test for presence of text string in cell

      forgot to add all I get is #VALUE! I can't get the "y"

      Comment


        #4
        Re: LibreOffice Calc: how to test for presence of text string in cell

        Do NOT use Calc's main menu "Find and replace" function (Ctrl+F). That is a different thing altogether. I'm working with inline cell functions. Call up LibreOffice's Help file and search for "IF function" - that's the beast I'm tangling with. Inside that, I'm using a "SEARCH function", as you'll see in a moment.

        Then, do this, in an empty spreadsheet:
        1. Enter "xxxx-flac" into cell A1.
        2. Enter "=IF(SEARCH("-flac",A1),"y","") into cell A2.
        3. You should get a "y" in A2, when you hit enter.

        Now, remove the "-flac" substring from A1. Bingo --- you see my problem. I want an empty cell (the "" in the IF function), but I'm getting #VALUE!, which is a "no result" outcome, according to the "### error message" article in the help file. With that result, the IF cannot finish, so I never get my desired empty cell.

        I hope I'm more clear now...and thanks for your reply.

        Comment


          #5
          Re: LibreOffice Calc: how to test for presence of text string in cell

          Originally posted by tomcloyd
          Here's the function I'm trying to use:
          Code:
          =IF(SEARCH("-flac",C15),"y","")
          . If the target cell contains, say, "xxxxx -flac", I get a "y" result, but if not I get "#VALUE!" as output.
          Try with:
          Code:
          =IF(ISNUMBER(SEARCH("-flac",C15)),"y","")
          EDIT:
          You can also use ISERROR, if you reverse the results you want...
          http://wiki.services.openoffice.org/...EARCH_function

          Comment


            #6
            Re: LibreOffice Calc: how to test for presence of text string in cell

            Originally posted by tomcloyd

            I hope I'm more clear now...and thanks for your reply.
            =NOT(ISERROR(SEARCH("flac";A1)))

            Comment


              #7
              Re: LibreOffice Calc: how to test for presence of text string in cell

              Nice. Both kubicle's and molostoff's solutions work.
              Windows no longer obstructs my view.
              Using Kubuntu Linux since March 23, 2007.
              "It is a capital mistake to theorize before one has data." - Sherlock Holmes

              Comment


                #8
                Re: LibreOffice Calc: how to test for presence of text string in cell

                Yes, thank you both much. Nice responses! Where there's a will there's a forum to find the way...heh heh...

                Comment


                  #9
                  Re: <SOLVED> LibreOffice Calc: how to test for presence of text string in cell

                  In a much belated manner I agree that the posts work.

                  woodsmoke

                  Comment

                  Working...
                  X