FIND & REPLACE rows of text in an Excel worksheet

A

afterdinnerspeaker

Board Regular
Joined
Jan 10, 2019
Messages
70
Here is my problem:

I’m trying to create a macro to find & replace certainrows of text in an Excel worksheet.


Example:

[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 31, bgcolor: transparent"] 5710
[/TD]
[TD="width: 387, bgcolor: transparent"] Electric in Pasture (CLAIM 100%)
[/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"]
0.00 ​
[/TD]
[TD="width: 19, bgcolor: transparent"] Dr
[/TD]
[/TR]
</tbody>[/TABLE]
Replace with:
[TABLE="width: 536"]
<tbody>[TR]
[TD="width: 38, bgcolor: transparent"] 5710
[/TD]
[TD="width: 387, bgcolor: transparent"] Electric in Pasture (CLAIM 100%)
[/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"]
0.00 ​
[/TD]
[TD="width: 26, bgcolor: transparent"] Dr
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 31, bgcolor: transparent"] 5720
[/TD]
[TD="width: 387, bgcolor: transparent"] Electric in Yard (represents 66.6%)
[/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"]
0.00 ​
[/TD]
[TD="width: 19, bgcolor: transparent"] Dr
[/TD]
[/TR]
</tbody>[/TABLE]
Replace with:
[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 31, bgcolor: transparent"] 5720
[/TD]
[TD="width: 387, bgcolor: transparent"] Electric in Yard (figures represent 66.6%)
[/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"]
0.00 ​
[/TD]
[TD="width: 19, bgcolor: transparent"] Dr
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 31, bgcolor: transparent"] 5940
[/TD]
[TD="width: 387, bgcolor: transparent"] Telephone / Landline (rep. 33.3%)
[/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"]
0.00 ​
[/TD]
[TD="width: 19, bgcolor: transparent"] Dr
[/TD]
[/TR]
</tbody>[/TABLE]
Replace with:
[TABLE="width: 526"]
<tbody>[TR]
[TD="width: 31, bgcolor: transparent"] 5940
[/TD]
[TD="width: 387, bgcolor: transparent"] Telephone / Landline (figures represent 33.3%)
[/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"][/TD]
[TD="width: 59, bgcolor: transparent"]
0.00 ​
[/TD]
[TD="width: 19, bgcolor: transparent"] Dr
[/TD]
[/TR]
</tbody>[/TABLE]


Many thanks in advance,

Jerry


 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Assume A1 is original text,

C1=SUBSTITUTE(SUBSTITUTE(A1,"(represents","(figures represent "),"(rep.","(figures represent ") copy down
 
Upvote 0
Here is a better representation of my spreadsheet:


<tbody> [TD="width: 42, bgcolor: transparent"]
A​
[/TD]
[TD="width: 174, bgcolor: transparent"]
B​
[/TD]
[TD="width: 110, bgcolor: transparent"]
C​
[/TD]
[TD="width: 111, bgcolor: transparent"]
D​
[/TD]
[TD="width: 111, bgcolor: transparent"]
E​
[/TD]
[TD="width: 111, bgcolor: transparent"]
F​
[/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 437, bgcolor: transparent, colspan: 4"] XXXXXXXXXX 2017 [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 548, bgcolor: transparent, colspan: 5"] Transactions by Account Report 01/01/2017 to 31/12/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 437, bgcolor: transparent, colspan: 4"] Sorted by: Date [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] Date [/TD]
[TD="width: 111, bgcolor: transparent"] Comment [/TD]
[TD="width: 111, bgcolor: transparent"] Source # [/TD]
[TD="width: 111, bgcolor: transparent"] Debits [/TD]
[TD="width: 111, bgcolor: transparent"] Credits [/TD]
[TD="width: 111, bgcolor: transparent"] Balance [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 42, bgcolor: transparent"] 5710 [/TD]
[TD="width: 729, bgcolor: transparent, colspan: 6"] Electric in Pasture (CLAIM 100%) REPLACE WITH: “Electric in Pasture (CLAIM 100%)” [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 18/01/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] electric SW 30-21-5-4 pasture (CLAIM 100%) [/TD]
[TD="width: 111, bgcolor: transparent"] ATCO Electric [/TD]
[TD="width: 111, bgcolor: transparent"] 36.22 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 36.22 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 15/02/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] electric SW 30-21-5-4 pasture (CLAIM 100%) [/TD]
[TD="width: 111, bgcolor: transparent"] ATCO Electric [/TD]
[TD="width: 111, bgcolor: transparent"] 30.73 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 66.95 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 17/03/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] electric SW 30-21-5-4 pasture (CLAIM 100%) [/TD]
[TD="width: 111, bgcolor: transparent"] ATCO Electric [/TD]
[TD="width: 111, bgcolor: transparent"] 32.92 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 99.87 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] 5720 [/TD]
[TD="width: 729, bgcolor: transparent, colspan: 6"] Electric in Yard (represents 66.6%) REPLACE WITH: “Electric in Yard (figures represent 66.6%)” [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 20/01/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] electric SW 28-21-5-4 including house (figures represent 66.6%) [/TD]
[TD="width: 111, bgcolor: transparent"] Direct Energy [/TD]
[TD="width: 111, bgcolor: transparent"] 101.10 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 101.10 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 17/02/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] electric SW 28-21-5-4 including house (figures represent 66.6%) [/TD]
[TD="width: 111, bgcolor: transparent"] Direct Energy [/TD]
[TD="width: 111, bgcolor: transparent"] 123.74 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 224.84 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 21/03/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] electric SW 28-21-5-4 including house (figures represent 66.6%) [/TD]
[TD="width: 111, bgcolor: transparent"] Direct Energy [/TD]
[TD="width: 111, bgcolor: transparent"] 110.91 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 335.75 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 111, bgcolor: transparent"] [/TD]
[TD="width: 42, bgcolor: transparent"] 5860 [/TD]
[TD="width: 729, bgcolor: transparent, colspan: 6"] Natural Gas (represents 50%) REPLACE WITH: “Natural Gas (figures represent 50%)” [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 10/01/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] gas utilities including house (figures represent 50%) [/TD]
[TD="width: 111, bgcolor: transparent"] Dry Country Gas [/TD]
[TD="width: 111, bgcolor: transparent"] 152.27 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 152.27 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 08/02/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] gas utilities including house (figures represent 50%) [/TD]
[TD="width: 111, bgcolor: transparent"] Dry Country Gas [/TD]
[TD="width: 111, bgcolor: transparent"] 124.55 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 276.82 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
[TD="width: 42, bgcolor: transparent"] [/TD]
[TD="width: 174, bgcolor: transparent"] [/TD]
[TD="width: 110, bgcolor: transparent"] 07/03/2017 [/TD]
[TD="width: 111, bgcolor: transparent"] gas utilities including house (figures represent 50%) [/TD]
[TD="width: 111, bgcolor: transparent"] Dry Country Gas [/TD]
[TD="width: 111, bgcolor: transparent"] 57.28 [/TD]
[TD="width: 111, bgcolor: transparent"] 0.00 [/TD]
[TD="width: 111, bgcolor: transparent"] 334.10 [/TD]
[TD="width: 111, bgcolor: transparent"] Dr [/TD]
</tbody>
 
Upvote 0
Just a suggestion since I'm attempting to REPLACE only certain specific text, would the solution include making a LIST of the specific text I want to replace?
 
Upvote 0
Further to my question:

There are only 3 strings of text that need replacing in each worksheet:


  • “Electric in Pasture (CLAIM 100%)” TO “Electric in Pasture (CLAIM 100%)”
  • “Electric in Yard (represents 66.6%)” TO “Electric in Yard (figures represent 66.6%)”
  • “Natural Gas (represents 50%)” TO “Natural Gas (figures represent 50%)”

Many thanks,

Jerry
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Find And Replace in Multi excel files with multi worksheets
2
Replies
15
Views
2K
Fluff
Fluff
R
  • Question Question
Find and Replace strings in all worksheets in all workbooks in folder
Replies
0
Views
438
rmk911
R
E
  • Question Question
VBA Dictionary Macro to find and replace (translate) text
2
Replies
19
Views
17K
LPMM_PT
L
V
  • Question Question
Macro code to find and replace text in different worksheets based on master worksheet data
Replies
2
Views
4K
vigneshvenkat
V
W
  • Question Question
Find text in single column and copy selected cells from same row in next empty row of different worksheet
Replies
0
Views
709
withquestions
W

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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