how to remove data appears in two columns on same row

RettaJ

New Member
Joined
Feb 6, 2008
Messages
9
Hi everyone,
I'm stuck on how to do this. I have one column that shows a series number for a set of products & another column that may or may not include that series number along with the description. I want to remove the series number from the description column ie,
I2=R13, I5=R13 SomeWidget.
I want I5=SomeWidget.
I would appreciate any suggestions.
RettaJ
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If you put this formula in I6, say

=TRIM(SUBSTITUTE(I5,I2,""))

and copy it across all the relevant columns

then select row 6, choose copy, then paste special -> values

You can then delete row 5

Try this on a copy of your data
 
Upvote 0
RettaJ,

If the two columns were I and J.

Before the macro:


Excel Workbook
IJ
1Series NumberProducts
2R13R13 SomeWidget
3R14AnotherWidget
4R15R15OldWidget
5R16NewWidget
6R17R17 SomeWidget
7R18NewerWidget
8R19R19 WidgetCity
9R20R20 WidgetWidget
Sheet1



After the macro:


Excel Workbook
IJ
1Series NumberProducts
2R13SomeWidget
3R14AnotherWidget
4R15OldWidget
5R16NewWidget
6R17SomeWidget
7R18NewerWidget
8R19WidgetCity
9R20WidgetWidget
Sheet1




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).


Code:
Option Explicit
Sub Check2Columns()
Dim i As Long
Dim strI As String, strJ As String, Hold As String
For i = 2 To Cells(Rows.Count, 9).End(xlUp).Row
  strJ = Range("J" & i).Value
  strI = Range("I" & i).Value
  If Left(strJ, Len(strI)) = strI Then
    Hold = Right(strJ, Len(strJ) - Len(strI))
    Range("J" & i).Value = Trim(Hold)
  End If
Next i
End Sub


Then run the "Check2Columns" macro.


Have a great day,
Stan


Have a great day,
Stan
 
Last edited:
Upvote 0
The formula worked beautifully! Thank you so much! You have no idea how much time you have saved me! Thank you,Thank you, Thank you!
RettaJ
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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