HUGE problems with detecting multiple text strings a single cell

BillTony

Board Regular
Joined
May 3, 2017
Messages
58
I am trying to find a method in which to detect 2 separate Text Strings within a given cell.

In a nutshell - user input on a form I have is not always what it could be.

So, if the expected answer would be "No Luck," some users will invariably enter "No Luck."

Unfortunately, according to the specifications I have been supplied, the latter is "acceptable..."

Below I'm including 2 snippets that work perfectly well independently of each other - the issue I am having is how to COMBINE them into a single statement (with AND somehow?).

I've tried quite a number of variations so far, but haven't come up with anything usable!

Thanks in advance!

Code:
=IF(ISNUMBER(SEARCH("No",A1)),"Text String Found","Not present")

=IF(ISNUMBER(SEARCH("Luck",A1)),"Text String Found","Not present")
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

BillTony

Board Regular
Joined
May 3, 2017
Messages
58
Sorry, the 2nd "No Luck" in the example had several spaces between the 2 words - I guess the forum logic "proof-read" them out...

Thanks again!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
How about
=IF(AND(ISNUMBER(SEARCH("No",A1)),ISNUMBER(SEARCH("Luck",A1))),"Text String Found","Not present")
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

This could also work:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";"> no   luck  </td><td style="text-align: right;;"></td><td style=";">Text String Found</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet92</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">"*No*Luck*",A1</font>)</font>),"Text String Found","Not Present"</font>)</td></tr></tbody></table></td></tr></table><br />
 

BillTony

Board Regular
Joined
May 3, 2017
Messages
58
Thanks to you BOTH for such timely responses to a thorny issue - I will give it a shot as soon as I return to the office!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,350
Messages
5,443,948
Members
405,258
Latest member
daveyf

This Week's Hot Topics

Top