looking for excel function help

  • Thread starter Thread starter ExcelNewb
  • Start date Start date
E

ExcelNewb

column A and B
need column B to look in A's Data
and search for -xxxx and -yyyy
if data in A contains any -xxxx or -yyyy letters
then B shows Yes, if not shows No

How can this be done, plz help

:(
 
Newb

In B1 enter =IF(OR(A1="-xxxx",A1="-yyyy"),"Yes","No")

Drag/copy down column B.

Gord Dibben Excel MVP
 
One way, try:

=IF(AND(ISERROR(SEARCH("*"&"-xxxx"&"*",A1)),ISERROR(SEARCH("*"&"-yyyy"&"*",A
1))),"No","Yes")
 
thx for the fast reply
but its not working.
everything just goes No
I need it to search the data if contains -xxxx or -yyyy in the data
such as,

if A1 = Microsoft-xxxx
then B1 would display yes

if A2 = Microsoft-yyyy
then B2 would display yes

if A3 = Microsoft
then B3 would display N
 
Maybe give my suggestion a try, i.e.

Put in B1:
=IF(AND(ISERROR(SEARCH("*"&"-xxxx"&"*",A1)),ISERROR(SEARCH("*"&"-yyyy"&"*",A
1))),"No","Yes")

Copy B1 down col B as many rows as you have data in col A
 
Watch out for any inadvertent line wrap/break
when you paste the formula in the formula bar
(you have to clear the wraps/breaks via Delete/Backspace key
after pasting)

The one below should be better (free of line wrap/break)

Put in B1:

=IF(AND(ISERROR(SEARCH("*"&"-xxxx"&"*",A1)),ISERROR(SEARCH("*"&"-yyyy"&"*",A
1))),"No","Yes")
 
Max said:
*Watch out for any inadvertent line wrap/break
when you paste the formula in the formula bar
(you have to clear the wraps/breaks via Delete/Backspace key
after pasting)

The one below should be better (free of line wrap/break)

Put in B1:

=IF(AND(ISERROR(SEARCH("*"&"-xxxx"&"*",A1)),ISERROR(SEARCH("*"&"-yyyy"&"*",A
1))),"No","Yes")
Click to expand...
Thx for the help but its still not working
It just gives me a message saying the formula i typed contains a
error:confused
 
Thx for the help but its still not working
It just gives me a message saying the formula i typed contains an
error:confused:
Click to expand...

That's because you haven't cleared the line wraps / breaks
after you copy > pasted the formula.

Unfortunately, the line wrap / break was still present in the post

[Apologies for the apparently misleading line:
The one below should be better (free of line wrap/break) ]
Click to expand...

Put your cursor just before this the "1"
in this end part of the formula:

" .... 1))),"No","Yes") "

and press Backspace key, then Enter.

It should be ok now.
 
Back
Top