How to search for a word and replace entire cell with another word

E

excelblob

New Member
Joined
Jan 21, 2010
Messages
10
I've seen many examples of finding and replacing a specific text string. However, I want to find a word within a cell, then replace the entire cell with another word.

The searched cells may contain more than one word beyond the one I'm looking for.

I have this:

Sub FindData1ReplaceData2()

ActiveSheet.Activate
Cells.Replace What:="A", Replacement:="B", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False


End Sub

My cell may contain 'abcd'. This routine just replaces the 'a' with 'b' and the cell becomes 'bbcd'. I only need 'b' in the cell.

Seems pretty easy, but can't find the solution... :(
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Not tested but maybe:

Code: 
Sub FindData1ReplaceData2()

ActiveSheet.Activate
    Cells.Replace What:="A*", Replacement:="B", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False


End Sub

Why are you activating the active sheet....its already active or it wouldn't be the active sheet :)
 
Upvote 0
Code: 
Cells.Replace What:="*a*", Replacement:="b", LookAt:=xlPart,MatchCase:=False
 
Upvote 0
Thanks to both of you. It worked just like I wanted.

And jproffer, I've actually never thought of that activesheet command. Ha ha..I can leave that out from now on.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JeffGrant
  • Question
Find & Replace is searching whole workbook instead of just the sheet
Replies
7
Views
128
bobsan42
bobsan42
L
  • Question
Clean Text - Remove Repeat Line Breaks
Replies
4
Views
165
lneidorf
L
R
  • Solved
Excel VBA error when attempting to replace text that has multiple lines a cell
2
Replies
11
Views
363
Revco
R
R
  • Question
Replace links after moving worksheets from one workbook to another.
Replies
2
Views
117
RockandGrohl
R
S
  • Question
Excel macros extremely slow
Replies
4
Views
185
Shloime
S

Forum statistics

Threads
1,214,833
Messages
6,121,868
Members
449,053
Latest member
Mesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top