Libreoffice calc : search based on delimiter from cells, with multiple values

[newusr879]

My data in multiple rows, columns in a table :

S.no Name Gender

1 Robert Male

2 Theresa FeMale

3 John Male

4 Doe,Albert,Rosie Trans,Male,FeMale

5 Barbera FeMale

6 Smith,Mary,Peter Trans,FeMale,Male

How i want to display with search filtering, based on delimiter and Search value:

S.no Name Gender

1 Robert Male

3 John Male

4 Doe,Albert,Rosie Trans,Male,FeMale

6 Smith,Mary,Peter Trans,FeMale,Male

I want to select gender values based on Male, in which some values in cell are having multiple values.
Please tell how i can achieve this…?

Note :
With standard filter, items with text containing other strings like “FeMale” when we are searching with value of “Male” are shown.
Please provide a way with delimiter “,” or any other custom delimiter.

Thank you.

Thanks.

[mgl]

Hello,

LO does not work preferabily with Excel document (xls or xlsx).

Please edit your question to let us know if each work is in a separate cell (multi column table) or if 1 line above represents the content of a songle cell (single column table)

Kind regards, Michel

[newusr879]

Hello… its a multi column table with multiple rows and columns.

[JohnSUN]

@newusr879 Don’t you think that the spelling of “FeMale” is somewhat different from the grammatically correct form? Doesn’t the spell checker (Shift+F7 or Tools - Automatic Check Spelling) underline this word? Or are you trying to make the task as difficult as possible? Keep in mind - many of us can create datasets that break even the most reliable algorithm, but we don’t (usually).

[newusr879]

@JohnSUN

Yes you are right, its grammatically incorrect. But the dataset i presented is a very simple one.
In real world, this may not be as simple as it is, with the presented data set in this question. The answer you provided works with the dataset 60% accuracy for me.

I searched in internet for several hours before posting here… I hope when anyone searched in google, they dont waste time like me.

Its not like I am making it difficult task, but I am trying to achieve with a delimiter (as per my understanding, then it would work 100% and all edge cases).

[JohnSUN]

@newusr879 I don’t want to disappoint you, but binding to separators will not give 100% result either - sometimes spaces may appear after the delimiter, which will be inserted by the autocorrector and comparison with the pattern will give an incorrect result. (It was necessary to warn that your example of data with Male and FeMale is only a hypothetical example, and not your real problem - I would not waste time drawing animation, I would know that your nickname does not correspond to reality and we must to talk seriously, and not like a complete newbie)

[JohnSUN]

Hello and welcome!

The Standard filter is perfect for your task.

[newusr879]

Hi… thanks for ur answer… but it also shows items with text containing other strings like “FeMale” when we are searching with value of “Male”.

Please provide a way with delimiter “,” or any other custom delimiter.

Thank you.

[JohnSUN]

There is no need to bind to delimiters - you have to add delimiters at the beginning and end of a string to catch words not in the middle of the text. It’s easier to use \b in a regular expression. For example, an auxiliary column with the formula =ISERROR(REGEX(C2;"\bMale\b")) will successfully mark the rows in your table with TRUE and FALSE. You can filtered your data by FALSE - surprise! - using the Standard filter.

[Lupp]

The word “female” simply contains “male” like “clothing-catalogue” contains “cat”.
Such complications are among the many for what experienced users of any software working on data (kind of) strictly dissuade from mimicking data by compounds without a precisely defined structure.
The standard filter claims to be able to apply RegEx, but I don’t kno exactly in what way it does, and would therefore not rely on this claim.

[newusr879]

@Lupp
yes you are right… the text word, which has to be searched, can be found anywhere… as u have specified exactly… me too is in dark, on how standard filter gets applied to search term… anyways @JohnSUN 's guidance would work, but as the dataset increases in number and size, it may have its limitations in edge cases and may need some tweaks… thank you for ur comment…

[Lupp]

As I already told in a comment, compound data like those given here as examples shouldn’t be considered data at all. Never use such non-formats without any clear and assured (specified) structure yourself. Use csv strictly delivering one piece of data per column, and clearly coding “unused” in an unambiguous way, where a column isn’t used.

Alas! We all had cases again and again where even “well known companies” did flood and trash us with stuff like exemplified here.

What to do then?
My advice: Don’t try to work with the mess in the long run. Convert the delivered whatever to a data format, best obeying the basic normalizations applied with DB.

In some cases user code may be the means of choice, but generally the situation much improved by the implementation of the REGEX() function available since version 6.2.y.
Even in cases like the given one you can use it for the recommended conversion.
How to do so is demonstrated in the attached example. The example is not “the final solution”, however. There is neither a claim like “best practice” nor a guarantee of correctness or the like.

ask272686filteringAndOrConvertingCompoundsToRealData_1.ods