CODEWITHSUNDEEP
vbaexcel
Mathew Mitchell
Mathew Mitchell

Reputation: 73

Excel: Find and Replace Macro - One Column Only

I've written some macros to format a load of data into the same accepted format, the program we pull from refuses to pull the data how we want it but in theory it wouldn't be hard to change in Excel.

The way it is set to run is to have separate macros for the modifiers and then a 'Run All' macro that just does a Call to them all.

Currently I have:

Sub ReplaceTitleMs()
'
' Strips Mrs from Headteacher Name
'
'
'
 Columns("V").Select
 Cells.Replace What:="Ms ", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

But when I run this, it strips Ms from the whole sheet and one column requires Ms to still be in the Cells (this is column W)

An example of the data is effectively:

Ms Helen Smith
Ms Brenda Roberts
Ms Kirsty Jones

But there are many other titles being used so I would like to just run a Find and Replace on the column that has to be selected by the macro.

The macro works find on the column I want it to...I just need to restrict it to that column!

Upvotes: 6

Views: 89751

Answers (3)

Top-Master
Top-Master

Reputation: 8816

Search and Replace in selected-cells

  1. press the "ALT + F11" keys shortcut (i.e. hold ALT key down and press F11 key at last release both) to open the Microsoft Visual Basic for Applications window.

  2. Click "Insert > Module" (i.e. from "Insert" menu click "Module"), and paste the following code in the Module Window.

Sub ReplaceInSelection()
    Dim rng As Range
    Dim workRng As Range
    Dim find$
    Dim replace$
On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set workRng = Application.Selection
    Set workRng = Application.InputBox("Range", "Select Range", workRng.Address, Type:=8)
    find = Application.InputBox("Find:", "Select what to find")
    replace = Application.InputBox("Replace with:", "Select replacement")
    For Each rng In workRng
        If rng.Value = find Then
            rng.Value = replace
        End If
    Next
End Sub
  1. for now close Microsoft Visual Basic for Applications window
  2. and Select the range of url text that you want to convert to clickable hyperlinks.
  3. Then press "ALT + F8" key (or just F5 in older excel), to run the code click run while "ReplaceInSelection" is selected
  4. then a pop-up dialog will open for you to change previously selected range, then click OK
  5. then another pop-up dialog will open for to specify what you want to find
  6. then, at last, a pop-up dialog will open for you to specify with what you want to replace any occurrence of what was found

all done! the selected cells have been searched for what you specified to find and was changed with again what you specified to replace with.

Upvotes: 0

brettdj
brettdj

Reputation: 55692

Based on your data (which is properly capitilised), to avoid false matches you should set MatchCase to True to avoid culling the likes of Mrs Nancy Adams to Mrs Nancy Ada

Columns("V").Replace "Ms ", vbNullString, xlPart, xlByRows, True

To be completely bullet-proof, you would either

  1. Use AutoFilterfor the detection and removal
  2. Build a range of matches with Find and FindNext, then remove only the first three characters where the match is correct

Upvotes: 10

user2140173
user2140173

Reputation:

You need to properly qualify the range for the Replace() method 

Sub ReplaceTitleMs()
    Columns("V").Replace    What:="Ms ", _
                            Replacement:="", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End Sub

Upvotes: 16

Related Questions