FIND & REPLACE rows of text in an Excel worksheet

[afterdinnerspeaker]

Here is my problem:

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


Example:

5710

Electric in Pasture (CLAIM 100%)

0.00 ​

Dr

<tbody> </tbody>

Replace with:

5710

Electric in Pasture (CLAIM 100%)

0.00 ​

Dr

<tbody> </tbody>

5720

Electric in Yard (represents 66.6%)

0.00 ​

Dr

<tbody> </tbody>

Replace with:

5720

Electric in Yard (figures represent 66.6%)

0.00 ​

Dr

<tbody> </tbody>

5940

Telephone / Landline (rep. 33.3%)

0.00 ​

Dr

<tbody> </tbody>

Replace with:

5940

Telephone / Landline (figures represent 33.3%)

0.00 ​

Dr

<tbody> </tbody>

Many thanks in advance,

Jerry

 

[caabyyc]

Assume A1 is original text,

C1=SUBSTITUTE(SUBSTITUTE(A1,"(represents","(figures represent "),"(rep.","(figures represent ") copy down

 

[afterdinnerspeaker]

Thank you for your reply! Would you mind showing me the whole macro including declarations?

Many thanks, Jerry

 

[afterdinnerspeaker]

Here is a better representation of my spreadsheet:

A​	B​	C​	D​	E​	F​
XXXXXXXXXX 2017
Transactions by Account Report 01/01/2017 to 31/12/2017
Sorted by: Date
		Date	Comment	Source #	Debits	Credits	Balance
5710	Electric in Pasture (CLAIM 100%) REPLACE WITH: “Electric in Pasture (CLAIM 100%)”	0.00	Dr
		18/01/2017	electric SW 30-21-5-4 pasture (CLAIM 100%)	ATCO Electric	36.22	0.00	36.22	Dr
		15/02/2017	electric SW 30-21-5-4 pasture (CLAIM 100%)	ATCO Electric	30.73	0.00	66.95	Dr
		17/03/2017	electric SW 30-21-5-4 pasture (CLAIM 100%)	ATCO Electric	32.92	0.00	99.87	Dr
5720	Electric in Yard (represents 66.6%) REPLACE WITH: “Electric in Yard (figures represent 66.6%)”	0.00	Dr
		20/01/2017	electric SW 28-21-5-4 including house (figures represent 66.6%)	Direct Energy	101.10	0.00	101.10	Dr
		17/02/2017	electric SW 28-21-5-4 including house (figures represent 66.6%)	Direct Energy	123.74	0.00	224.84	Dr
		21/03/2017	electric SW 28-21-5-4 including house (figures represent 66.6%)	Direct Energy	110.91	0.00	335.75	Dr
5860	Natural Gas (represents 50%) REPLACE WITH: “Natural Gas (figures represent 50%)”	0.00	Dr
		10/01/2017	gas utilities including house (figures represent 50%)	Dry Country Gas	152.27	0.00	152.27	Dr
		08/02/2017	gas utilities including house (figures represent 50%)	Dry Country Gas	124.55	0.00	276.82	Dr
		07/03/2017	gas utilities including house (figures represent 50%)	Dry Country Gas	57.28	0.00	334.10	Dr

<tbody> </tbody>

 

[afterdinnerspeaker]

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?

 

[afterdinnerspeaker]

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