• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to extract/copy/paste specific texts/words among a sentence in a cell

Young Fang

New Member
0

I want to search/find "fire" or "combustion" or "ignition" in column C. the entries at every row of column c are sentences, not just a word (which is relatively easier).

Once "fire" or "combustion" or "ignition" could be found, then copy the entire row (entire columns or selective columns) and transfer the data to the other workbook or mater workbook.

My question is: how to find a word (fire, combustion,ignition) in a sentence in a cell? removing duplicate (data validation) may be needed after searching.
 
Young Fang

Firstly, Welcome to the Chandoo.org Forums

Can you post a sample file with an example of before and after just to clarify what you want
 
Young Fang

Firstly, Welcome to the Chandoo.org Forums

Can you post a sample file with an example of before and after just to clarify what you want

Thanks a lot for your reply. I uploaded a sample file about before and after the process.
 

Attachments

  • Before and after.xlsx
    10.7 KB · Views: 8
Hi:

Please find the attached. I have flagged the data you can filter it and copy it to the new sheet,or else give a look up on the new tab based on the flag I have given.

Thanks
 

Attachments

  • Before and after.xlsx
    11.4 KB · Views: 3
I have achieved that using Formula
See attached:
 

Attachments

  • Before and after-1.xlsx
    11.2 KB · Views: 4
Hi:

Please find the attached. I have flagged the data you can filter it and copy it to the new sheet,or else give a look up on the new tab based on the flag I have given.

Thanks

Thanks for your help.

Could you do that by VBA coding, initiate the program by a Macro? not by formula, because I have ten similar workbooks, each worksheet has thousands rows.

Have a Good day.
 
I have achieved that using Formula
See attached:

Could you do that by VBA coding, initiate the VBA routine by a Macro? not by formula, because I have ten similar workbooks, each worksheet has thousands rows.

Have a Good day.
 
Hi:

Please find the attached.

Thanks

Really appreciate it for your help. You did this by auto-filter the header of "flag"--"Copy".

Could you do it without "flag" column, just search the "descriptions" column, using search criteria like "fire" or "ignition" or "combustion" ? (search specific word from a sentence in a cell)

One more question is: in C2 cell, "fire","ignition","fires" all of these 3 match the search criteria, but I just want to copy this row once,not 3 times. How to remove the duplicate ones?

Thanks and have a great day,
 

Hi !

So easy with an advanced filter !​
Code:
Sub Demo()
Application.ScreenUpdating = False
Worksheets("After").Cells(1).CurrentRegion.Clear

With Worksheets("Before")
    .Cells(11).Value = .Cells(3).Value
      .[K2:K4].Value = [{"*combustion*";"*fire*";"*ignition*"}]
    .Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[K1:K4], Worksheets("After").Cells(1), True
    .[K1:K4].Clear
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
Hi !

So easy with an advanced filter !​
Code:
Sub Demo()
Application.ScreenUpdating = False
Worksheets("After").Cells(1).CurrentRegion.Clear

With Worksheets("Before")
    .Cells(11).Value = .Cells(3).Value
      .[K2:K4].Value = [{"*combustion*";"*fire*";"*ignition*"}]
    .Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[K1:K4], Worksheets("After").Cells(1), True
    .[K1:K4].Clear
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
Hi !

So easy with an advanced filter !​
Code:
Sub Demo()
Application.ScreenUpdating = False
Worksheets("After").Cells(1).CurrentRegion.Clear

With Worksheets("Before")
    .Cells(11).Value = .Cells(3).Value
      .[K2:K4].Value = [{"*combustion*";"*fire*";"*ignition*"}]
    .Cells(1).CurrentRegion.AdvancedFilter xlFilterCopy, .[K1:K4], Worksheets("After").Cells(1), True
    .[K1:K4].Clear
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !

Thanks, Let me try with your suggestions.
 
Back
Top