Identifying which cells contain curses

[Topher]

I'm looking for a formula to help me identify which cells (all in one column) contain swear words. (There are at least 20 words I would like this formula to determine if the cell contains)

In the column (K) of 35,000 cells of lyrics, about half of the cells have line breaks in them if that makes any difference in how the formula is written.

A simple Yes/No would more or less be adequete to help with this issue, but if there's a way to actually identify which word is used, that would be better.

I plan to then sort the whole sheet by the results that this formula will produce and then examine and edit each line from there.
All things depending, I would probably plan to copy this column, and then "paste special > values" to cut down on calculation time.

But anyways
Thanks a lot!

Guess we all have got to keep it rated PG for MR.Excel

 

[Topher]

This will also help me a great deal in identifying and easily grouping (via sort tool) which "lyrics cells" contain all sorts of other words, not exclusively the swear words.

 

[Jaye7]

I am not an expert, however you could use something like the following, it is case sensitive though.

Sub Macro1()
 Range("K1:K35000").Select
 
Dim Cell As Range
For Each Cell In Selection
  If Cell.Value = "f1" Then
  Cell.Value = "Yes"
  End If
  
   If Cell.Value = "f2" Then
  Cell.Value = "Yes"
  End If
  
Next Cell
End Sub

You just change the f1 or f2 to the words and copy that section down for however many words you desire.

ie.

Sub Macro1()
Range("K1:K35000").Select

Dim Cell As Range
For Each Cell In Selection
If Cell.Value = "f1" Then
Cell.Value = "Yes"
End If

If Cell.Value = "f2" Then
Cell.Value = "Yes"
End If
 
If Cell.Value = "f3" Then
Cell.Value = "Yes"
End If

If Cell.Value = "f4" Then
Cell.Value = "Yes"
End If


Next Cell
End Sub

 

[Jaye7]

I am not great at script writing, however you can record the script yourself using the find and replace which will do what you want as follows.

If you use my script just change the f1, f2 and Yes values and then add others as required.

Sub Macro2()
Range("K1:K35000").Select
Selection.Replace What:="f1", Replacement:="YES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
Selection.Replace What:="f2", Replacement:="YES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
 
End Sub

 

[Jaye7]

Sorry please ignore the 1st script that I sent, my internet wasn't working so I didn't think that it had posted, then when I read what you wanted again I realised my error as the 1st script matched whole cell values only.

 

[Aladin Akyurek]

I'm looking for a formula to help me identify which cells (all in one column) contain swear words. (There are at least 20 words I would like this formula to determine if the cell contains)

In the column (K) of 35,000 cells of lyrics, about half of the cells have line breaks in them if that makes any difference in how the formula is written.

A simple Yes/No would more or less be adequete to help with this issue, but if there's a way to actually identify which word is used, that would be better.

I plan to then sort the whole sheet by the results that this formula will produce and then examine and edit each line from there.
All things depending, I would probably plan to copy this column, and then "paste special > values" to cut down on calculation time.

But anyways
Thanks a lot!

Guess we all have got to keep it rated PG for MR.Excel

Create a range housing the swear words you want to look for and name this range SwearWords.

In L2 invoke and copy down:

Either...

=LOOKUP(9.99999999999999E+307,SEARCH(SwearWords,K2),SwearWords)

Or...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&SwearWords&" "," "&K2&" "),SwearWords)

 

[Topher]

Much appreciated!

Although I'm not very familiar with VBA's. I was able to put the macro into the Visual basic for the sheet I want. And I switched "f1" to one of the words I am looking for. But that's about it (I then click macros in the ribbon and a window pops up and I select to run Macro1... but all it does is highlight column K.

I dunno how to run it and produce the results I need.
For instance I want the results to be shown in the adjacent column L

 

[Topher]

Create a range housing the swear words you want to look for and name this range SwearWords.

In L2 invoke and copy down:

Either...

=LOOKUP(9.99999999999999E+307,SEARCH(SwearWords,K2),SwearWords)

Or...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&SwearWords&" "," "&K2&" "),SwearWords)

OMG my brother you rock. That was perfect.
Exactly what I needed.
I have no idea how it works, but it works!

and a guy praising... ray:
Thank you

Now time to try and make good use of it.

 

[Topher]

I am not great at script writing, however you can record the script yourself using the find and replace which will do what you want as follows.

If you use my script just change the f1, f2 and Yes values and then add others as required.

Sub Macro2()
Range("K1:K35000").Select
Selection.Replace What:="f1", Replacement:="YES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
Selection.Replace What:="f2", Replacement:="YES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
 
 
End Sub

Thanks for helping me Jaye7

 

[Jaye7]

Try this, not perfect but as good as I can achieve.

Copy the script into a module and then select and run it as you did with the other script.

Sub Test1()
 Columns("K:K").Select
    Selection.Copy
    Columns("L:L").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    Selection.Replace What:="*f1*", Replacement:="YES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
    Selection.Replace What:="*f2*", Replacement:="YES", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 
    Range("L1").Select
    Application.CutCopyMode = False
 
Range("L1:L35000").Select
Dim Cell As Range
For Each Cell In Selection
If Cell.Value <> "YES" Then
Cell.Value = ""
End If
 
Next Cell
 
Range("K1").select
 
End Sub

for extra words copy the following into the script before the
Range("L1").Select
and replace the f2 portion with your word.
You can change the YES part to whatever you want but you must replace every occurance of it within the full script

Selection.Replace What:="*f2*", Replacement:="YES", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False