Text within a Cell

  • Thread starter Thread starter N1KO
  • Start date Start date
N

N1KO

Hi,

I could do with either some VBA code or excel formula to find a selection of
letters (Eg. PDQ) within a cell.

The letters could be anywhere in the cell and can be of any format Eg PdQ.

I need it to return a value of 1

Cheers
 
Hi,

Take your pick. As a formula

=IF(NOT(ISERROR(SEARCH("pdq",A1))),1,"")

In VBA

If InStr(Range("A1"), "pdq") Then
MyVal = 1
End If

Mike
 
=isnumber(search("pdq",a1))

will return true or false

=search() ignores case
=find() is case sensitive

Another case-insensitive way:
=countif(a1,"*pdq*")>0
or
=countif(a1,"*"&"pdq"&"*")>0
or if the pdq is in another cell:
=countif(a1,"*"&x99&"*")>0
 
For a formula version, try this...

=LEFT("1",COUNTIF(A1,"*PDQ*")=1)

The above returns 1 if the text is in the cell and the empty string
otherwise. If you wouldn't mind a 0 being returned if the text is not in the
cell, then the above can be shortened to this...

=--(COUNTIF(A1,"*PDQ*")=1)
 
I see Dave posted a COUNTIF method as well; however, his ">0" test is better
than my "=1" test (just in case the "PDQ" text appears more than once in the
cell), so I would use that instead...

=LEFT("1",COUNTIF(A1,"*PDQ*")>0)

or

=--(COUNTIF(A1,"*PDQ*")>0)

depending on what you want to happen, as I posted in my original message, if
"PDQ" is not in the text.
 
See, this is what happens when I answer questions before having my morning
cup of coffee... my original test "=1" is fine to use (and, hence, my
original formulas are fine to use as well)... multiple occurrences of "PDQ"
in a **single** cell are only counted once with the COUNTIF function call I
posted. The need for ">0" would come in if we were testing *multiple* cells
for the occurrence of that text... since we are only testing a *single*
cell, that COUNTIF function call can only return 0 or 1.
 
=countif() counts the number of cells with the entry--not the number of times
the entry occurs in the cell.

But if that cell could be empty, it would be better to check

=if(a1="","it's empty",if(countif(a1,"*pdq*")>0,"Yep","nope")))
 
I think the Internet must be running slow today. Your first message in this
thread (which you sent 8 minutes before my first message in this thread)
didn't become visible to me in my newsreader until some 13 minutes after I
posted my first message in this thread. I'm guessing from the wording you
used in your last message that you didn't see my third posting in this
thread which I show as arriving in the thread about 20 minutes before your
last message. I have seen the Internet run slow like this before and today
(or at least currently) it seems to be doing so again.

Anyway, as I said in my other post... I hadn't had my morning cup of coffee
yet (that has now been rectified<g>)... as a matter of fact, I had only just
awakened for the day some 5 or 10 minutes before joining this thread. As for
this formula...
=if(a1="","it's empty",if(countif(a1,"*pdq*")>0,"Yep","nope")))
Click to expand...

well, we have no idea if it is a good way to go or not as the OP only told
us what he wanted for one of the three possible conditions (leaving us to
guess at the rest). At least he now has several offerings to choose from...
one of them has to meet his needs.
 
Back
Top