Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text within a Cell
Look in the vba help index for FIND -- Don Guillett Microsoft MVP Excel SalesAid Software "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy text from cell to cell with one cell changing text | Excel Worksheet Functions | |||
Setting the text in a cell to the text of a cell on a different sheet in the same workbook | Excel Programming | |||
select text in cell based on text from another cell, paste the text at the begining of a thrid cell, etc... | Excel Programming | |||
Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |