Need to filter worksheet by Cells that Contain specific characters

[mstuf]

Good Evening -- I have a spreadsheet that I need to filter and display rows that in a specifued Column, CONTAIN specified Character Values.

IE I Need to filter and display ( preferably in a seperate sheet ) all rows that contain CD in Column AK . Typical Values in that column would be CD International - Box Set CD & DVD - CD Rock - LP Blues - VHS Music Rock - Book Paperback Science Biology - Cassette Jazz - Audio CD Fiction Humor - etc.



After doing the Advanced Filter Tutorials and then searching for the syntax to define Contains, I have found that filtering by "Contains" feature is N/A.

Would someone be kind enough to share knowledge and give me some ideas of where to look next for the best way to do this. My Excel Skills are some above beginner but, by no means advanced.

Thank You

 

[Yogi Anand]

Good Evening -- I have a spreadsheet that I need to filter and display rows that in a specifued Column, CONTAIN specified Character Values.

IE I Need to filter and display ( preferably in a seperate sheet ) all rows that contain CD in Column AK . Typical Values in that column would be CD International - Box Set CD & DVD - CD Rock - LP Blues - VHS Music Rock - Book Paperback Science Biology - Cassette Jazz - Audio CD Fiction Humor - etc.



After doing the Advanced Filter Tutorials and then searching for the syntax to define Contains, I have found that filtering by "Contains" feature is N/A.

Would someone be kind enough to share knowledge and give me some ideas of where to look next for the best way to do this. My Excel Skills are some above beginner but, by no means advanced.

Thank You

Hi mstuf:

Let us have a look at the following illustration ...

Book4
	A	B	C	D	E	F	G	H
1	Field1	Field2	Field3					database
2	A_1	Box CD	C_1		5			criteriaRange
3	A_2	CD	C_2					copyToRange
4	A_3	Music	C_3
5	A_4	Music CD	C_4		Field2	Field3
6	A_5	Songs	C_5		Box CD	C_1
7	A_6	ultra	C_6		CD	C_2
8	A_7	techno	C_7		Music CD	C_4
9	A_8	jazz	C_8		Jay CD Kay	C_9
10	A_9	Jay CD Kay	C_9		CDs galore	C_10
11	A_10	CDs galore	C_10
12	A_11	Sony Music	C_11
13
Sheet1

I have used computed criterion with the following formula in cell E2 ...

=FIND("CD",B2)

I hope this helps.

 

[Stormseed]

then searching for the syntax to define Contains

hi,

When you apply an autofilter on this column, try specifying the syntax '*CD*' (without quotes) in the "Contains" option and then do a filter. It should work.

 

[mstuf]

Thank you both for the ideas -- I am at work now but will be home a little later to experiment with those ideas and see what works for me.

Again - Thank you - I really appreciate all the help this board has provided.

 

[pbornemeier]

Try this code:

Sub CopyMatchingLines()

Dim lngX As Long
Dim lngY As Long
Dim lngLastDataRow As Long
Dim strInputWorksheet As String
Dim strOutputWorksheet As String
Dim strFilterFor As String
Dim strFilterColumn As String

'Set parameters
strInputWorksheet = "Sheet1"
strOutputWorksheet = "Sheet2"
strFilterFor = "CD"
strFilterColumn = "AK"

'Delete all data on target worksheet
Worksheets(strOutputWorksheet).Select
Cells.Clear
Range("A1").Select

'Copy data from input worksheet to target worksheet
Worksheets(strInputWorksheet).Select
lngLastDataRow = ActiveCell.SpecialCells(xlLastCell).Row

lngY = 0
For lngX = 1 To lngLastDataRow
If InStr(Range(strFilterColumn & CStr(lngX)), strFilterFor) > 0 Then
lngY = lngY + 1
Sheets(strInputWorksheet).Rows(lngX).Copy
Sheets(strOutputWorksheet).Select
Rows(lngY).Select
Range("A" & CStr(lngY)).Activate
ActiveSheet.Paste
Worksheets(strInputWorksheet).Select
End If
Next
End Sub

 

[PATSYS]

Good Evening -- I have a spreadsheet that I need to filter and display rows that in a specifued Column, CONTAIN specified Character Values.

IE I Need to filter and display ( preferably in a seperate sheet ) all rows that contain CD in Column AK . Typical Values in that column would be CD International - Box Set CD & DVD - CD Rock - LP Blues - VHS Music Rock - Book Paperback Science Biology - Cassette Jazz - Audio CD Fiction Humor - etc.



After doing the Advanced Filter Tutorials and then searching for the syntax to define Contains, I have found that filtering by "Contains" feature is N/A.

Would someone be kind enough to share knowledge and give me some ideas of where to look next for the best way to do this. My Excel Skills are some above beginner but, by no means advanced.

Thank You

You do not need advanced filetering for this. The normal AUTOFILTERING can handle this. Do Autofilter, Custom, in the Title chose Contain then type CD in the provided field.

 

[mstuf]

SWEET -

Well, I learned something from each example -- Thank you

I think the last one is the easiest for this project -- Seems so easy now that I see it but, Nowhere in my reading Excel Help did I run across that option. I had assumed that since Contains was not an operator in advanced that it was not for Auto Either.

In trying each of the others, I find parts of those that will help with some other ideas that have been rolling around in my head wondering if or how I could do it.

Looking at the Code from pbornemeier I went thru step by step - I dont think its my answer but two routines in it are answers to other back of the brain ideas.


As Always - Amazed by how much some of you know about Excel - I like to just browse and read here while watching TV.

I got to feel smart the other day when I watched a friend working repetitivly on a work sheet and was able to set him up with a formula and 2 macros to save more 50% of his manual operations. What a Rush !!


Thanks