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?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
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.
 

i-m-t

Board Regular
Joined
Jul 12, 2007
Messages
100
I am sorry you are right. I have two columns as B and D. The list is going upto B120 and D120. Sorry.
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077

ADVERTISEMENT

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
 

i-m-t

Board Regular
Joined
Jul 12, 2007
Messages
100
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.
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077

ADVERTISEMENT

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.
 

i-m-t

Board Regular
Joined
Jul 12, 2007
Messages
100
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.
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,777
Members
414,020
Latest member
Meghdad

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
Top