| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find text - worksheet function or macro?
I have text in A2-A1500. All of the cells contain long strings of text. I
want to identify which cells contain "ItemA" or "ItemB". As I am weak in VB I would prefer to use worksheet functions if possible. I tried using FIND but that returns #VALUE! if the string does not exist. All suggestions welcome! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find text - worksheet function or macro?
One way ..
Put in B2: =SUMPRODUCT(--ISNUMBER(FIND({"ItemA","ItemB"},A2)))0 Copy down as far as required to B1500 Col B returns TRUE if either: "ItemA" or "ItemB", or both, are found. Just autofilter col B for TRUE to get all the lines Replace FIND with SEARCH if you don't want the searching to be case sensitive (SEARCH is not case sensitive) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sebh007" wrote: I have text in A2-A1500. All of the cells contain long strings of text. I want to identify which cells contain "ItemA" or "ItemB". As I am weak in VB I would prefer to use worksheet functions if possible. I tried using FIND but that returns #VALUE! if the string does not exist. All suggestions welcome! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find text - worksheet function or macro?
Fantastic! Thank you so much!
S "Max" wrote: One way .. Put in B2: =SUMPRODUCT(--ISNUMBER(FIND({"ItemA","ItemB"},A2)))0 Copy down as far as required to B1500 Col B returns TRUE if either: "ItemA" or "ItemB", or both, are found. Just autofilter col B for TRUE to get all the lines Replace FIND with SEARCH if you don't want the searching to be case sensitive (SEARCH is not case sensitive) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sebh007" wrote: I have text in A2-A1500. All of the cells contain long strings of text. I want to identify which cells contain "ItemA" or "ItemB". As I am weak in VB I would prefer to use worksheet functions if possible. I tried using FIND but that returns #VALUE! if the string does not exist. All suggestions welcome! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find text - worksheet function or macro?
Pleasure` !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sebh007" wrote in message ... Fantastic! Thank you so much! S |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find text - worksheet function or macro?
Sorry, but there's more. I get the resuLt 'TRUE' or 'FALSE' which is a great
help but there are other tests I wish to apply elsewhere so I need to be able to say the equivalent of =IF(A2='TRUE',1,0) or similar. Although the display in A2 is definitely 'TRUE' Excel doesn't recognise it as such. Is there any way of utilising the 'TRUE' that is being returned in A2 please? Thanks in advance. S "Max" wrote: Pleasure` ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sebh007" wrote in message ... Fantastic! Thank you so much! S |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find text - worksheet function or macro?
It's OK thanks - cracked it! Just bracket it with an IF and I'm there -
sorry to trouble you. S "sebh007" wrote: Sorry, but there's more. I get the resuLt 'TRUE' or 'FALSE' which is a great help but there are other tests I wish to apply elsewhere so I need to be able to say the equivalent of =IF(A2='TRUE',1,0) or similar. Although the display in A2 is definitely 'TRUE' Excel doesn't recognise it as such. Is there any way of utilising the 'TRUE' that is being returned in A2 please? Thanks in advance. S "Max" wrote: Pleasure` ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "sebh007" wrote in message ... Fantastic! Thank you so much! S |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find text - worksheet function or macro?
Glad you got it sorted out !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- sebh007 wrote: It's OK thanks - cracked it! Just bracket it with an IF and I'm there - sorry to trouble you. S |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calling worksheet function from a macro | Excel Worksheet Functions | |||
| Macro: Find and replace | Excel Discussion (Misc queries) | |||
| Sorting - Macro or worksheet function | Excel Worksheet Functions | |||
| hide a worksheet so that a macro can still find it | Excel Worksheet Functions | |||
| Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) | |||
Linear Mode
