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

August 2017




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


Published by jak58. Latest update on September 6, 2012 at 08:21 AM by jad05.
This document, titled "Excel/VBA - Deleting a string of character in a range of cells," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).