Relplacing Words which contain "/" without quotes.

[ANONYMOUS123456]

Hello Everyone,
I have excel column A where each cell contains a English paragraph. I want to remove all those words which contain "/" without quotes anywhere in the paragraph. I don't know how to do this.

Regards,

 

[pgc01]

Hi

I don't understand. Incomplete information.

Please clarify

- the / is at the beginning, at the end, in the middle of the word?
- can there be more than 1 such words in the cell?

Post examples of paragraphs before and after the words removed.

 

[Llupo01]

Hello,

If I understood correctly, this should help you...if anywhere in column A is found "/", row will be deleted.

Sub delete()

Dim cell As Range
Dim ContainWord As String
Dim rng As Range

'Range?
  Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Rows

'text?
  ContainWord = "/"

  For Each cell In rng.Cells
    If Not cell.Find(ContainWord) Is Nothing Then cell.EntireRow.delete
  Next cell
  

End Sub

 

[ANONYMOUS123456]

Hello,

If I understood correctly, this should help you...if anywhere in column A is found "/", row will be deleted.

Sub delete()

Dim cell As Range
Dim ContainWord As String
Dim rng As Range

'Range?
  Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Rows

'text?
  ContainWord = "/"

  For Each cell In rng.Cells
    If Not cell.Find(ContainWord) Is Nothing Then cell.EntireRow.delete
  Next cell
  

End Sub

I don''t want to delete that row, I just want to delete that word which contain / . All the remaining contents of that cell should remain intact.

 

[Llupo01]

Then just replace ,,entirerow.delete" with ,,Clearcontents" as per code below.

Sub delete()

Dim cell As Range
Dim ContainWord As String
Dim rng As Range

'Range?
  Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Rows

'text?
  ContainWord = "/"

  For Each cell In rng.Cells
    If Not cell.Find(ContainWord) Is Nothing Then cell.ClearContents
  Next cell
  

End Sub

 

[ANONYMOUS123456]

Hi

I don't understand. Incomplete information.

Please clarify

- the / is at the beginning, at the end, in the middle of the word?
- can there be more than 1 such words in the cell?

Post examples of paragraphs before and after the words removed.

The / could be at the beginning, end or middle of the word. Yes there could be more than 1 word containing / . I just want to remove that specific word while keeping the remaining paragraph intact.

 

[ANONYMOUS123456]

Hi

I don't understand. Incomplete information.

Please clarify

- the / is at the beginning, at the end, in the middle of the word?
- can there be more than 1 such words in the cell?

Post examples of paragraphs before and after the words removed.

Then just replace ,,entirerow.delete" with ,,Clearcontents" as per code below.

Sub delete()

Dim cell As Range
Dim ContainWord As String
Dim rng As Range

'Range?
  Set rng = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Rows

'text?
  ContainWord = "/"

  For Each cell In rng.Cells
    If Not cell.Find(ContainWord) Is Nothing Then cell.ClearContents
  Next cell
  

End Sub

Dear brother,
I want to delete that specific string containing / . I want to keep remaining paragraph intact in that cell. E.g " I can try all things ... help plz https:// glowpink.com/4744/lose-weig ht-try-things-help-plz " is in A1 cell. I want the output " I can try all things ... help plz".

 

[Peter_SSs]

Assuming data in column A and results to go into column B, try this with a copy of your data.

Sub RemoveWords()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = " \S*?/\S*? "
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    a(i, 1) = Application.Trim(RX.Replace(" " & Replace(a(i, 1), " ", "  ") & " ", ""))
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

 

[ANONYMOUS123456]

Assuming data in column A and results to go into column B, try this with a copy of your data.

Sub RemoveWords()
  Dim RX As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = " \S*?/\S*? "
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    a(i, 1) = Application.Trim(RX.Replace(" " & Replace(a(i, 1), " ", "  ") & " ", ""))
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

Thank you very much peter. This works perfect. I want to know one thing more. e.g If I want remove those words which contain * then which line of VBA code I have to change. Actually I want to save this code for future if I want to remove the words containing sth else.

 

[Peter_SSs]

Thank you very much peter. This works perfect.

You are welcome.

I want to know one thing more. e.g If I want remove those words which contain * then which line of VBA code I have to change.

Is this a separate removal or combined with the "/" removals? Again, 2 or 3 samples & expected results would help.

.. if I want to remove the words containing sth else.

I'm not sure what that means.