Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Finding text within text
Hello,
I have two columns with text. I'd like take those rows that contain multiple "keywords" and put them on a separate sheet. For example, a few of the keywords would be: gift, check, personal, error and if that cell contained any of those keywords then it would be put on a separate sheet. Is this possible? Thank you! |
#2
|
|||
|
|||
Hi!
Need a better explanation. Some examples would also help. What does "I have two columns with text" have to do with it? Does that mean 1 column may contain a keyword and the 2nd column may also contain a keyword and in those rows where both columns have keywords you want those 2 entries extracted to another location? Biff "Cathy Landry" wrote in message ... Hello, I have two columns with text. I'd like take those rows that contain multiple "keywords" and put them on a separate sheet. For example, a few of the keywords would be: gift, check, personal, error and if that cell contained any of those keywords then it would be put on a separate sheet. Is this possible? Thank you! |
#3
|
|||
|
|||
Hi Biff,
You're right, I was not very explicit in what I need. I'm working with a spreadsheet that has data in col a through aa. columns r/x contain "notes" regarding corp/purchase card expenditures. I need to look for certain words within those two columns and copy the entire row/s to another sheet. We want to look at those individuals that are making purchases that fall outside of our company's policies and procedures. A few of the keywords would be "gift", "personal", "party", "error", "check" etc. Thank you! Cathy "Biff" wrote: Hi! Need a better explanation. Some examples would also help. What does "I have two columns with text" have to do with it? Does that mean 1 column may contain a keyword and the 2nd column may also contain a keyword and in those rows where both columns have keywords you want those 2 entries extracted to another location? Biff "Cathy Landry" wrote in message ... Hello, I have two columns with text. I'd like take those rows that contain multiple "keywords" and put them on a separate sheet. For example, a few of the keywords would be: gift, check, personal, error and if that cell contained any of those keywords then it would be put on a separate sheet. Is this possible? Thank you! |
#4
|
|||
|
|||
Hi!
Since you want the entire row of data "copied" to another sheet I think you should use filters. Based on your description this would require too many formulas to be efficient. Take a look he http://contextures.com/tiptech.html There you'll find just about everything you always wanted to know about filters! Biff "Cathy Landry" wrote in message ... Hi Biff, You're right, I was not very explicit in what I need. I'm working with a spreadsheet that has data in col a through aa. columns r/x contain "notes" regarding corp/purchase card expenditures. I need to look for certain words within those two columns and copy the entire row/s to another sheet. We want to look at those individuals that are making purchases that fall outside of our company's policies and procedures. A few of the keywords would be "gift", "personal", "party", "error", "check" etc. Thank you! Cathy "Biff" wrote: Hi! Need a better explanation. Some examples would also help. What does "I have two columns with text" have to do with it? Does that mean 1 column may contain a keyword and the 2nd column may also contain a keyword and in those rows where both columns have keywords you want those 2 entries extracted to another location? Biff "Cathy Landry" wrote in message ... Hello, I have two columns with text. I'd like take those rows that contain multiple "keywords" and put them on a separate sheet. For example, a few of the keywords would be: gift, check, personal, error and if that cell contained any of those keywords then it would be put on a separate sheet. Is this possible? Thank you! |
#5
|
|||
|
|||
Hi Biff, thank you for your input.
Here's the code that I'm using courtesy of Jindon. For anyone else that's looking to extract text within text this is perfect. Sub FindAndCopy() Dim r As Range, i As Long Dim Phrase Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Sheets("Sheet1") Set ws2 = Sheets("Sheet2") Phrase = Array("gift", "check", "wrong") With ws1 For Each r In Intersect(.Range("r:x"), .UsedRange) For i = LBound(Phrase) To UBound(Phrase) If InStr(1, r, Phrase(i), vbTextCompare) 0 Then r.EntireRow.Copy _ ws2.Range("A65536").End(xlUp).Offset(1, 0) Exit For End If Next Next End With MsgBox "done" Application.CutCopyMode = False End Sub "Biff" wrote: Hi! Since you want the entire row of data "copied" to another sheet I think you should use filters. Based on your description this would require too many formulas to be efficient. Take a look he http://contextures.com/tiptech.html There you'll find just about everything you always wanted to know about filters! Biff "Cathy Landry" wrote in message ... Hi Biff, You're right, I was not very explicit in what I need. I'm working with a spreadsheet that has data in col a through aa. columns r/x contain "notes" regarding corp/purchase card expenditures. I need to look for certain words within those two columns and copy the entire row/s to another sheet. We want to look at those individuals that are making purchases that fall outside of our company's policies and procedures. A few of the keywords would be "gift", "personal", "party", "error", "check" etc. Thank you! Cathy "Biff" wrote: Hi! Need a better explanation. Some examples would also help. What does "I have two columns with text" have to do with it? Does that mean 1 column may contain a keyword and the 2nd column may also contain a keyword and in those rows where both columns have keywords you want those 2 entries extracted to another location? Biff "Cathy Landry" wrote in message ... Hello, I have two columns with text. I'd like take those rows that contain multiple "keywords" and put them on a separate sheet. For example, a few of the keywords would be: gift, check, personal, error and if that cell contained any of those keywords then it would be put on a separate sheet. Is this possible? Thank you! |
#6
|
|||
|
|||
Cathy Landry wrote...
.... Here's the code that I'm using courtesy of Jindon. For anyone else that's looking to extract text within text this is perfect. Sub FindAndCopy() Dim r As Range, i As Long Dim Phrase Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Sheets("Sheet1") Set ws2 = Sheets("Sheet2") Phrase = Array("gift", "check", "wrong") With ws1 For Each r In Intersect(.Range("r:x"), .UsedRange) This uses all columns between columns R and X, inclusive. Your previous follow-up made it seem you wanted to look through only columns R and X. For i = LBound(Phrase) To UBound(Phrase) If InStr(1, r, Phrase(i), vbTextCompare) 0 Then r.EntireRow.Copy _ ws2.Range("A65536").End(xlUp).Offset(1, 0) Exit For End If Next Next End With MsgBox "done" Application.CutCopyMode = False End Sub .... You could use a macro for this, but it'd be simpler to do this with an advanced filter. All columns in your original table would need field names in the top row. You'd then need a criteria range with the field names for columns R and X in another range and the words to match in separate rows as follows. Col_R_Field Col_X_Field *gift* *check* *wrong* *gift* *check* *wrong* Filter in place, copy the filtered rows, and paste into another worksheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improvements for text finding functions | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
Finding the mode of text | Excel Discussion (Misc queries) | |||
Finding Partial Text in a Cell | Excel Worksheet Functions |