Menu

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

Ask a question



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
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Related