Ask Your Question

calc: how to check if a string contains any of the values in a given cell range [closed]

asked 2013-11-27 14:09:59 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Is there any way to check if a given string cell contains any of the values in a given set of cells? I made a "database" of keywords to build up Cad layer's name from them.
I can select what I'm going to draw from some lists and it gives me the suggested name, as an example: the layer to draw Contractor/Shop Drawings: Walls, Redlines, Wood (Future work) shall be named Z-WALL-REDL-WOOD-F
Now I wish to suggest also line weight and line type to be used. So I made new tables of keywords to associate keywords to, let's say, line weight. So for keywords like "bold line" or "section" or "outline" a large line should be used.
So now I shall check the content of the layer's name if it contains ANY of the given words.
I found the text function "SEARCH" (Ricerca in my Italian version) that can find a word inside a string. Now I need a formula to check all the words inside the given cell range "bold Layers".
Is there any formula, possibly avoiding a macro?
Any help will be appreciated. The Calc file will be distributed with a CC license.
Thank you
Please find my work-in-progress calc file at this link:

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-16 00:06:06.734798

2 Answers

Sort by » oldest newest most voted

answered 2013-12-02 14:11:32 +0200

unichan gravatar image

updated 2013-12-03 14:29:34 +0200

Here's a solution: first I had to learn arrays, and the final formula is:


(input it as an array formula) where "layername" is the cell with the text wich content I have to analize
"Keywords.F4:F11" is the array of words that, if "layername" has any of them, the output of the formula will be "hidden".
Please find attached the file (as always, rename it as *.ods). In the first sheet you can find the single steps of the formula to reach my intent: LayerDbase(NCS5)test.png
You will notice that only when added the OR clause the result become a cell from an array.
Thank you for your enlightning help
A new version is available, closer to my wish: LayerDbase(NCS5).png, but is there any way to compact those long series of if/else, and make them more powerfull and universal? For example please have a look at the Line Type formula. A better soution would be "for every value in the list do something".
That's again an array. But it's starting to be an array inside an other. Too much for me.

edit flag offensive delete link more

answered 2013-11-28 00:35:08 +0200

m.a.riosv gravatar image

updated 2013-11-29 00:37:00 +0200

Please @unichan, can you share a sample file with what you want achieve?, depending on how data are organizer can be different solutions.

edited 20131129

Please take a look to see if is what you are looking for.


change the extension to ods, there is an issue in the site, that only files with a graphic extension can be upload.

edit flag offensive delete link more


Thank you for your interest. This forum should really allow us to attach libreoffice files. Please find my work-in-progress calc file at this link: LayerDbase(NCS5) I added some comments in the file trying to make things clearer, but do not heistate to ask questions. There is a main tab that makes the string combinations, The other tab are all data tables. The keywords tables/database can be rearranged.

unichan gravatar imageunichan ( 2013-11-28 10:56:14 +0200 )edit

hallo @mariosv, I just checked your file. It take me a lot to understand that you're using matrix/array functions. Now I'm studying them. Your formula seems to work only on the first letter, and it gives "aaa" only if the first letter is "Z", else it doesn't seems to match any other keywords. If I understund, your function's result is still a matrix/array. Next step should be to check the control value against any of the matrix's values, and the result should be true if there is any match. I'm working on that. Thank you for showing me the direction.

unichan gravatar imageunichan ( 2013-12-02 11:51:33 +0200 )edit

Question Tools

1 follower


Asked: 2013-11-27 14:09:59 +0200

Seen: 20,279 times

Last updated: Dec 03 '13