How delete words in text by macro

i-m-t

Board Regular
Joined
Jul 12, 2007
Messages
100
Hi everyone. I prepared a huge list of formulas in two rows (A2 and A5). On the left row there are product codes and on the right row there are product descriptions. In the description text, the product code and product specifications are written. What I would like to do is to prepare a macro so that it will check A2 and A5 and when it finds the same code, it should delete the code from the text on A5 so that the code wil only be available on A2. This should continue to A120. Would you please help me?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
im really confused by what you want

you have 2 rows that you want remove text from one of the rows but then you say continue to A120

can you post sample of the sheet using one of the html tools

or can you explain it better.
 
Upvote 0
I am sorry you are right. I have two columns as B and D. The list is going upto B120 and D120. Sorry.
 
Upvote 0
I am sorry you are right. I have two columns as B and D. The list is going upto B120 and D120. Sorry.

ok,

and you want it to remove the product code from D which is basically found in column B

post a sample.

this isnt hard at all. if its what im thinking just a simple grab the value from B then do a replace in D
 
Upvote 0
I wish I could but I do not know how to compare two colums and delete a part of text using a macro :( I can only make very simple things like copy - paste etc by macro.
 
Upvote 0
we will help you.

but we need some sample data.

i got an idea on how to code it but without sample data I can't get too far.
 
Upvote 0
For example on B column there are these values:

SMQR 501 L
SMQR 300 A
SMSC 500 SC

And on column D there are these values:

SMQR 501 L red leds, satin stainless steel...
SMQR 300 A blue leds, mirror stainless steel...
SMSC 500 SC red leds, mirror stainless steel...

This goes upto row 125 that way. I would like to take out the text from column Dwhich is the same on column B.
 
Upvote 0
Code:
Sub hey()

Dim bcol As String, dcolnew As String
Last = Range("B65536").End(xlUp).Row

For x = 1 To Last
    bcol = Cells(x, 2).Value
    dcolnew = Cells(x, 4).Value
    dcolnew = Replace(dcolnew, bcol, "", 1)
    Cells(x, 4).Formula = dcolnew
Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,218,575
Messages
6,143,313
Members
450,477
Latest member
teresab543

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