How do I find a word in a string in a cell and copy that row?

[freder85ico]

Hi everybody

I'm a beginner when it comes to code and using excel except the basics, but I'm handling large worksheets with 17 columns and varying rows (100-2000)

Some of the column cells have very large strings
I currently only use the Find command to find cells with "Wnt" in the large string and then manually go and copy that row into a new worksheet

I've come across macros but they don't seem to work, I believe b/c the cells have a bunch of words and not just "Wnt"

Is there a way to do this automatically? Especially now I'm dealing with 50 "Wnt" hits

Any help would be greatly appreciated!!!

 

[jbeaucaire]

Even manually this should be easy. Turn on the DATA > FILTER > Autofilter for that data set, then use the drop down for that column to do a custom filter for "contains: Wnt".


Now you're looking at all the matching rows, copy them all at once...

 

[freder85ico]

Wow thank you!

What if I wanted rows that had "Wnt" in column A OR column B?

 

[hiker95]

freder85ico,


Welcome to the MrExcel forum.


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
2	2	Find Wnt here	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	Wnt
5	5	with 50 "Wnt" hits	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6
7	7	7	7	7	and not just "Wnt"	7	7	7	7	7	7	7	7	7	7	7	7
8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8
9	9	9	9	9	9	9	9	Wnt	9	9	9	9	9	9	9	9	9
10	10	10	10	10	10	10	10	10	10	Find Wnt here	10	10	10	10	10	10	10
11
Sheet1

After the macro in a new worksheet Wnt:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	2	Find Wnt here	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
2	5	with 50 "Wnt" hits	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
3	7	7	7	7	and not just "Wnt"	7	7	7	7	7	7	7	7	7	7	7	7
4	9	9	9	9	9	9	9	Wnt	9	9	9	9	9	9	9	9	9
5	10	10	10	10	10	10	10	10	10	Find Wnt here	10	10	10	10	10	10	10
6	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	Wnt
7
Wnt

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub FindWnt()
' hiker95, 08/16/2011
' http://www.mrexcel.com/forum/showthread.php?t=572250
Dim w1 As Worksheet, wW As Worksheet
Dim LR As Long, LC As Long, NR As Long
Dim c As Range, firstaddress As String
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Wnt!A1)") Then Worksheets.Add(After:=w1).Name = "Wnt"
Set wW = Worksheets("Wnt")
wW.UsedRange.Clear
NR = 0
LR = w1.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
LC = w1.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
With w1.Range(w1.Cells(1, 1), w1.Cells(LR, LC))
  Set c = .Find("*" & "Wnt" & "*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      NR = NR + 1
      w1.Rows(c.Row).Copy wW.Rows(NR)
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
wW.UsedRange.Columns.AutoFit
wW.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the FindWnt macro.

 

[jbeaucaire]

A non-macro solution for multiple columns would require you to add another column to your data to test by.

If column M were empty, you could put the search string in M1 Wnt and then this array formula in M2:

=OR(ISNUMBER(SEARCH($M$1, A2:B2)))
...confirmed with Ctrl-Shift-Enter, and copied down.

Now you can change M1 to whatever string you want, then filter by that column.


A non-array formula that might work for you could be:

=ISNUMBER(MATCH("*"&$M$1&"*",A2:B2,0))