VBA Find and Replace from Cell Contents

[jwalkins]

Hi,

I'm quite new to VBA and I'm struggling to find a solution to this. I'm trying to delete the value from Column 2 and Column 3 if it contains the value in its corresponding row in Column 1. i.e. for the first row delete 94000 from Column 3, then loop to the next column and delete 50000 from the Column 2. The searched for text needs to be the value in Column 1 and delete the value in Column 2 / 3 if it matches that of Column 1.

Any help would be much appreciated!

Column 1	Column 2	Column 3
94000	SAN RAMON	CA 94000
50000	CA, 50000
0400	NH	03780-0400
60000	Batavia, IL	60000
90000	CA	90000

<tbody>
</tbody>

 

[GlennUK]

Why don't you have calculation columns 4 and 5 to get the cleaned versions of columns 2 and 3?

Like:
=SUBSTITUTE(B2,$A2,"")

 

[jwalkins]

Thanks for the quick response GlennUK. I hadn't actually thought about using SUBSTITUTE so I gave it a go, the issue that appears now is beacuse the table is a "paste values" from another sheet with formulas so excel has stored the number as text in column 1, meaning the SUBSTITUTE formula isn't working as it should. Is there a work around to make the numbers past as numbers (and keep the 0 at the front of the number?)

Thanks

 

[GlennUK]

Try formatting the target area as Text prior to the copy/paste operation.