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

[tomcloyd]

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:

=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!

[woodsmoke]

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

[woodsmoke]

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"

[tomcloyd]

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.

[kubicle]

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

Here's the function I'm trying to use:

=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:

=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

[molostoff]

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

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

=NOT(ISERROR(SEARCH("flac";A1)))

[Snowhog]

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

Nice. Both kubicle's and molostoff's solutions work.

[tomcloyd]

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

[woodsmoke]

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