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:
Box
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.
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.
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.
Here’s a solution: first I had to learn arrays, and the final formula is:
=IF(OR(ISNUMBER(SEARCH(Keywords.F4:F11;layername)));"hidden";"")
(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
Update
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.
