[VBA] Deleting a word in a range of cell

Last update on July 3, 2009 10:04 AM by jak58
Published by jak58

[VBA] Deleting a word in a range of cell







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 SupprimerMot()
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
Best answers for « Deleting a word in a range of cell » in :
Delete duplicates in an Excel column ShowDelete duplicates in an Excel column To remove duplicates in an Excel column: Click on the Data menu Filter Advanced Filter In this menu, select the column where the duplicates Check the box "Extract without duplication"...
[Word]Delete all links Show[Word]Delete all links A common factor when you copy/paste part of a webpage on Word,is that you end up with a document containing a lot of hypertext lin: Here are two solutions to remove these links from a Word...
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Download Word Viewer Show
Download Quick Word to PDF ShowTo transfer your documents most in possible security, there is not better than format PDF. Then, to convert your documents Word into PDF, adopt the quick program Word to PDF. Quick Word to PDF is a tool which integrates with the interface of...
Download Free PDF to Word Converter ShowDocuments PDF are certainly most on and the most stable for transfers, since they cannot be changed. However, sometimes they need data contained in a document PDF which they would like to edit or to change. Free PDF to Word Converter is as name...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
They need your help