Using "Contains" in a formula instead of AutoFilter?

[Kirkebbs]

The Contains function in Auto filter is very powerful, because it looks for the string being asked for. The string being asked for could be in the middle of many, many characters or just a few. The problem with a vlookup, or index/match in a formula is it requires each character be accounted for. For example, I want to extract the word "RED" out of the following string THWPRED&Y4D and YTRED43E. The autofilter CONTAINS command will get RED out of each cell. However, because of the different number of characters I couldn't use VLOOKUP or I/M commands, unless I used a couple of IF statements to account for each wild character required. Any suggestions?... Thanks Kirk

 

[Loomah]

hi
I'm probably not quite understanding what you're getting at here but could you use the FIND function to find wherabouts RED is in your string?
Summat like
=IF(ISERROR(FIND($L$1,H4)),"",H4)

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[DaleWatson123321]

Hi Kirkebbs,

I agree with Loomah... we're not certain as to what exacly your objective is.

However, in your heading, you mention using a formula. If, for example you want a formula to determine the number of records in a database list that contain specific text (such as RED), then you can achieve this using ONE formula.

Example... The range names I'm using are arbitrary - i.e. you can use whatever names you prefer.

Steps:

1) Assign the name "data" to your database.

2) Create a separate sheet and name it "Criteria".

3) On the Criteria sheet, assign the range name "NameToFind" to a cell. This is where you'll later be entering the text you want your formula to find.

4) On the Criteria sheet, enter the field name from your database that is the field name for the field containing the text you're searching for. (For this example, we'll say the field is named is "Category&quot

5) On the Criteria sheet, in the cell immediately below where you have entered the name "Category", enter the following formula...
="*"&NameToFind&"*"

6) For these two cells mentioned in #4 and #5, highlight both cells and assign the range name "crit" (short for criteria)

7) You're now ready to create your formula. If you want to count the number of records that contain the word "RED" for example, enter the following formula in whatever cell you prefer.

=DCOUNTA(data,"Category",crit)

8) Now enter whatever text you wish into the cell you've named "NameToFind", and your formula will tell you the number of records containing that text.

If you should want to SUM the values in another field for those records containing the text you enter into the cell "NameToFind", then use the following formula...
=DSUM(data,"Quantity",crit) - where you replace the example field "Quantity" with whatever field name you use for the field you want to SUM the values from.

I hope you've been able to follow these steps. You've apparently already discovered some of the power of the AutoFilter options. These database formulas are "the next step up" in terms of "POWER". Once you learn how to write and set up the criteria, you'll become QUITE impressed at how "DYNAMIC" and "POWERFUL" they can be in terms of being able to extract precisely the data you're searching for. You can, in effect, create criteria as complex as is necessary to meet your needs.

For example, one could specify criteria to SUM OR COUNT data, or EXTRACT the records to a separate sheet, where you need to include all those records where:
a) the text "RED" is found,
b) for vehicles sold within a specified dollar range,
c) within cities having a population greater than 50,000
d) within a specified time period.
...and the list can go on, and on...

Please appreciate I'm unsure of your level of knowledge, so I'll include another couple of items, just in case, and also for the benefit of others who might be reading this.

Method of Assigning a Range Name:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

VBA routine for Filtering-in-place:

Sub Filter_InPlace()
Range(&quot;data&quot.AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range(&quot;crit&quot, _
Unique:=False
End Sub

VBA routine for extracting the data to a SEPARATE sheet:

Sub Extract_Data()
Range(&quot;data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=&quot;crit&quot;, _
CopyToRange:=Range(&quot;out&quot, _
Unique:=False
End Sub

Note that the above routine requires that you create the range name &quot;out&quot;. This name needs to be assigned to the top row of field names on your separate sheet. You should copy the field names from your database to the separate sheet. Do don't have to copy all the field names, and you can re-arrange the order of the names if you prefer.

CAUTION: By assigning the range name to the top row, any data below this top row will be deleted each time the routine is run. Therefore do NOT place any &quot;other&quot; data below this range.

I hope the above helps. Please advise as to how you make out. If you encounter any difficulty, perhaps you could provide more specifics, or email Loomah or myself a copy of your file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Loomah]

4kuneck Dale
You never cease to amaze me with the detail you're willing to go into!

Anyway, I've just noticed something that isn't clear with the formula I posted. &quot;$L$1&quot; is my &quot;criteria&quot; which is where I was puttng in &quot;RED&quot;.

clear as mudd?
;-) If a man says something and there are no women there to hear him, is he still wrong?