Excel/VBA - Deleting a string of character in a range of cells

November 2016




In the case you want to delete a word in a sentence, just create a small macro that removes the word.
But it will become difficult when you have word like, for example, "Theword" or "THEWORD" or "theword" .
Each of these word are different, but contains the same characters.

This little macro solves this problem.



Option Explicit    
Option Compare Text    


Sub DeleteWord()    
Dim Cel As Range, Range As Range     
Dim Word As String    
    Set Range = Range("B2:B20") '.    
    Word = "Theword"     
        
    Application.ScreenUpdating = False    
    For Each Cel In Range    
        If Cel Like "*" & Word & "*" Then    
            Cel = Replace(Cel, Word, "")    
           'To remove the double space that follows ..     
            Cel = Replace(Cel, "  ", " ")    
        End If    
    Next Cel    
    Application.ScreenUpdating = True    
End Sub

Related :

This document entitled « Excel/VBA - Deleting a string of character in a range of cells » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.