Can I use OR function with the SEARCH function in Libreoffice Calc?

English
#1

Goal: I want to search a column containing a moderate amount of text {Notes} for specific keywords and based on those keywords insert a text category (Example: “Pro-Trump” | “Anti-Trump” | “Pro-BLM”) into another column in a 46000 row spreadsheet.

Present formula I’m currently using:

IF(NOT(ISERROR(SEARCH("Martin Luther King",M2))),"Pro-MLK",
IF(NOT(ISERROR(SEARCH("pro-Biden",M2))),"Pro-Biden",
IF(NOT(ISERROR(SEARCH("anti-Biden",M2))),"Anti-Biden",
IF(NOT(ISERROR(SEARCH("Trump Supporters",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("Trump Supporter",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("anti-Trump",M2))),"Anti-Trump",
IF(NOT(ISERROR(SEARCH("People's Convoy",M2))),"Freedom Convoy",
IF(NOT(ISERROR(SEARCH("bomb threat",M2))),"Bomb Threat",
IF(NOT(ISERROR(SEARCH("bomb-making",M2))),"Bomb Making",
IF(NOT(ISERROR(SEARCH("Stop the Steal",M2))),"Pro-Legal Voting",
IF(NOT(ISERROR(SEARCH("support of President Trump",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("support President Trump",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("support * Trump",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("support* President Trump",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("support for President Trump",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("support * former President Donald Trump",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("Protect the Vote",M2))),"Pro-Legal Voting",
IF(NOT(ISERROR(SEARCH("anti-vaccine",M2))),"Anti-COVID Mandates",
IF(NOT(ISERROR(SEARCH("anti-mask",M2))),"Anti-COVID Mandates",
IF(NOT(ISERROR(SEARCH("mandate*",M2))),"Anti-COVID Mandates",
IF(NOT(ISERROR(SEARCH("protested President Biden",M2))),"Anti-Biden",
IF(NOT(ISERROR(SEARCH("support for gun rights",M2))),"Pro-2ND Amend",
IF(NOT(ISERROR(SEARCH("pro-Trump",M2))),"Pro-Trump",
IF(NOT(ISERROR(SEARCH("Black Lives Matter",M2))),"Pro-BLM",
IF(NOT(ISERROR(SEARCH("Black Lives Movement",M2))),"Pro-BLM",)))))))))))))))))))))))))

Question: Can the OR function be used with the SEARCH function in this formula to consolidate the formula throwing similar results such as “Pro-Trump” and “Pro-BLM”? I can’t seem to wrap my head around this. An example please if OR can be used. Thank you.

#3

Checking that I am reading you right: you want each entry to be tagged with one single category, namely the first one you have a match for in your formula?

#6

Yes, one primary classification per row. I realize that this can be subjective regarding what’s in the notes but it’s not realistic that I go through 46,000 rows one by one.

#2

Hi @JWSchrecker,
you can do several searches together by searching inside SUMPRODUCT(). This turns it into an array formula and you can find out with >0 if the text is there. You don’t know in which line, but that doesn’t matter.

=IF(SUMPRODUCT(IFERROR(SEARCH({"anti-Biden","protested President Biden"},M2),0))>0,"Anti-Biden",
IF(SUMPRODUCT(IFERROR(SEARCH({"anti-vaccine","anti-mask","mandate*"},M2),0))>0,"Anti-COVID Mandates",
IF(SUMPRODUCT(IFERROR(SEARCH({"anti-Trump"},M2),0))>0,"Anti-Trump",
IF(SUMPRODUCT(IFERROR(SEARCH({"bomb-making"},M2),0))>0,"Bomb Making",
IF(SUMPRODUCT(IFERROR(SEARCH({"bomb threat"},M2),0))>0,"Bomb Threat",
IF(SUMPRODUCT(IFERROR(SEARCH({"People’s Convoy"},M2),0))>0,"Freedom Convoy",
IF(SUMPRODUCT(IFERROR(SEARCH({"support for gun rights"},M2),0))>0,"Pro-2ND Amend",
IF(SUMPRODUCT(IFERROR(SEARCH({"pro-Biden"},M2),0))>0,"Pro-Biden",
IF(SUMPRODUCT(IFERROR(SEARCH({"Black Lives Matter","Black Lives Movement"},M2),0))>0,"Pro-BLM",
IF(SUMPRODUCT(IFERROR(SEARCH({"Stop the Steal","Protect the Vote"},M2),0))>0,"Pro-Legal Voting",
IF(SUMPRODUCT(IFERROR(SEARCH({"Martin Luther King"},M2),0))>0,"Pro-MLK",
IF(SUMPRODUCT(IFERROR(SEARCH({"Trump Supporters","Trump Supporter","support of President Trump","support President Trump","support * Trump","support* President Trump","support for President Trump","support * former President Donald Trump","pro-Trump"},M2),0))>0,"Pro-Trump",""))))))))))))

There may well be other possible solutions. That would be one.

#7

Thank you, I’m going to give this a try! I’ll let you know.

#9

Thank you dscheikey!

That solved my problem.

And thanks to everybody else for their input. I learned from all of you.

#4

@JWSchrecker Your approch is errorprone and fuzzy, imagine the Text in M2: we dont Protect the Vote
nevertheless see attached example:
black_live_matters.ods (10.5 KB)

#5

“supporting President Trump” is supposed to be “Pro-Trump”. That’s why @JWSchrecker used the placeholder.
Your approch @karolus brings also Pro-Legal Voting if you typing we dont Protect the Vote :slight_smile:
I think this is not the perfect approach for such a task.

#8

as I said before… every negatet phrase could be semantically mean the opposite!

#10

Just to satisfy curosity, this is the type of data that I’m dealing with - Sample;

On 18 January 2020, around 200 people and women gathered in Modesto (California) to participate in the Women’s March, rallying over reproductive rights, the protection of the LGBT community, and equal rights. Some of the people also protested against what they saw could be a possible war with Iran. [size=around 200]

On 14 January 2020, LGBTQ activists demonstrated outside the Virginia State Capitol in Richmond (Virginia) in support of a Senate anti-discrimination proposed bill. Bill 868 would add the categories ‘gender identity’ and ‘sexual orientation’ to the existing anti-discrimination laws. [size=no report]

On 18 December 2021, between 15 and 20 people organized by a local Pentecostal pastor gathered outside the Hayden Library in Hayden (Idaho) to demonstrate against an LGBT youth library program being hosted inside and against LGBT people in general. A demonstrator who was armed with a handgun and a knife entered a restricted area in front of the library, which had been established and clearly marked off following a previous demonstration of one of the program’s events, and was subsequently arrested by police for trespassing after he ignored police orders to leave the area. [size=between 15 and 20] [armed]

On 1 February 2020, hundreds of LGBT activists and their supporters gathered outside a public library in Seattle (Washington) to protest against the Women’s Liberation Front, a feminist organization that was holding an event at the library. Protesters asserted that the views of the group were discriminatory against trans peoples. Several activists entered the library and refused to leave, leading to the arrest of 2 by police officers. [size=several hundred]

On 26 August 2020, an explosive device was attached to the front door of a LGBT-owned gym in Gloucester City (New Jersey), causing damage to glass and door of the gym due to the explosion. The owners suspect the incident to be a hate crime against the LGBT owners of the gym.

On 30 June 2022, a group of people, including people who a local reporter identified as Proud Boys, demonstrated outside of a Pride event in Woodland (California), which was taking place inside Mojo’s Lounge and Bar. The demonstration turned violent when people ‘physically confronted’ the LGBT+ patrons of the event, and police say patrons sprayed pepper spray outside of the door of the bar in an effort to protect themselves from the confrontation. Police also state that there were several threats made in the days leading up to the event. Police say they are investigating the threats and violence as potential hate crimes. No injuries or arrests were reported. [size=no report]

On 11 July 2022, an unreported number of people marched from the Capitol to the White House in Washington DC - National Mall (District of Columbia) as part of the 1776 Restoration Movement, a splinter group from The People’s Convoy, to call for a ‘restoration of our constitutional republic, and return to a moral society.’ The group has been occupying a stretch of Madison Drive in front of the Smithsonian since 6 July as part of their protest. [size=no report]

On 10 July 2022, an unreported number of people affiliated with the 1776 Restoration Movement, a splinter group from The People’s Convoy, continued a demonstration in Washington DC - National Mall (District of Columbia) for the fifth consecutive day to call for a ‘restoration of our constitutional republic, and return to a moral society.’ The group has been occupying a stretch of Madison Drive in front of the Smithsonian since 6 July as part of their protest. [size=no report]

On 9 July 2022, people affiliated with the 1776 Restoration Movement, a splinter group from the People’s Convoy, continued a demonstration in Washington DC - National Mall (District of Columbia) for the fourth consecutive day to call for a ‘restoration of our constitutional republic, and return to a moral society.’ The group has been occupying a stretch of Madison Drive in front of the Smithsonian since 6 July as part of their protest. [size=no report]

So as you can see, the more out of over 40,000 rows that I can categorize with a formula, the better off I am.

#11

I think SWITCH function can help to make it easy. SWITCH function

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.