[Solved] If specific string is not detected or missing

Discuss the spreadsheet application

[Solved] If specific string is not detected or missing

Postby jhonpaul72 » Tue Apr 09, 2019 5:11 pm

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
jhonpaul72
 
Posts: 21
Joined: Mon Apr 08, 2019 3:38 pm

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

Postby Zizi64 » Tue Apr 09, 2019 5:15 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8207
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Zizi64 » Tue Apr 09, 2019 5:22 pm

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

Code: Select all   Expand viewCollapse view
=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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8207
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Zizi64 » Tue Apr 09, 2019 5:29 pm

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

Code: Select all   Expand viewCollapse view
=IF(SEARCH("Open SR";A1;1)=0;NA();"The string contains the text 'Open SR:'")
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8207
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby jhonpaul72 » Tue Apr 09, 2019 6:23 pm

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
jhonpaul72
 
Posts: 21
Joined: Mon Apr 08, 2019 3:38 pm

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

Postby Villeroy » Tue Apr 09, 2019 6:33 pm

=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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27085
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby jhonpaul72 » Wed Apr 10, 2019 12:22 pm

Here's the sample.
Attachments
Untitled 1(1).ods
(9.91 KiB) Downloaded 10 times
OpenOffice 4.1.3 on Windows 7
jhonpaul72
 
Posts: 21
Joined: Mon Apr 08, 2019 3:38 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 21 guests