ExcelBanter - Text within a Cell

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Text within a Cell (https://www.excelbanter.com/excel-programming/435605-text-within-cell.html)

Text within a Cell

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

Text within a Cell

=--ISNUMBER(FIND("PDQ",UPPER(A1)))

"N1KO" wrote:

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

Text within a Cell

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

"N1KO" wrote:

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

Text within a Cell

=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

N1KO wrote:

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

--

Dave Peterson

Text within a Cell

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)

--
Rick (MVP - Excel)

"N1KO" wrote in message
...
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

Text within a Cell

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.

--
Rick (MVP - Excel)

"Rick Rothstein" wrote in message
...
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)

--
Rick (MVP - Excel)

"N1KO" wrote in message
...
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

Text within a Cell

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.

--
Rick (MVP - Excel)

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)

"Rick Rothstein" wrote in message
...
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)

--
Rick (MVP - Excel)

"N1KO" wrote in message
...
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

Text within a Cell

=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")))

Rick Rothstein wrote:

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.

--
Rick (MVP - Excel)

"Rick Rothstein" wrote in message
...
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)

--
Rick (MVP - Excel)

"N1KO" wrote in message
...
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

--

Dave Peterson

Text within a Cell

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")))

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.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
=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")))

Rick Rothstein wrote:

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.

--
Rick (MVP - Excel)

"Rick Rothstein" wrote in message
...
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)

--
Rick (MVP - Excel)

"N1KO" wrote in message
...
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

--

Dave Peterson