[Solved] If specific string is not detected or missing

Discuss the spreadsheet application
Post Reply
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

[Solved] If specific string is not detected or missing

Post by jhonpaul72 »

Hi all. Hope someone could help me. I'm trying to formulate a function wherein if a string (example is "Open SR:") is not detected or missing on a specified cell (let's say Cell A1) then return "N/A" on cell A2.
Last edited by jhonpaul72 on Mon Apr 15, 2019 6:57 pm, edited 2 times in total.
OpenOffice 4.1.3 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: If a specific string is not detected or missing on a cel

Post by Zizi64 »

The "Open SR:" is the full content of the cell (when it exists), or it is a part of a longer text?

Please upload an ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: If a specific string is not detected or missing on a cel

Post by Zizi64 »

when the cell contains the specific text (only) or not:

Code: Select all

=IF(A1<>"Open SR:";NA();"the cell text equals to 'Open SR:'")
Last edited by Zizi64 on Tue Apr 09, 2019 5:31 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: If a specific string is not detected or missing on a cel

Post by Zizi64 »

If the "Open SR:" is a part only of the cell content:

Code: Select all

=IF(SEARCH("Open SR";A1;1)=0;NA();"The string contains the text 'Open SR:'")
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: If a specific string is not detected or missing on a cel

Post by jhonpaul72 »

The string "Open SR:" Is only part of the cell. Below is the example.

P: Mr. Doe needs help with SR: 4-1234567890

Company name: ABC 123
Name: John Doe

Open SR: 4-1234567890


R: Redirected
Last edited by jhonpaul72 on Tue Apr 09, 2019 7:20 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: If a specific string is not detected or missing on a cel

Post by Villeroy »

=IF(ISERR("blah";A1);"missing";"exists")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jhonpaul72
Posts: 24
Joined: Mon Apr 08, 2019 3:38 pm

Re: If a specific string is not detected or missing on a cel

Post by jhonpaul72 »

Here's the sample.
Attachments
Untitled 1(1).ods
(9.91 KiB) Downloaded 60 times
OpenOffice 4.1.3 on Windows 7
Post Reply