Hello, I am having some trouble determining a formula that would help me with my problem below.
I have a large Excel sheet and am hoping you can recommend a formula that will extract data from one cell, if that same data is found in another cell right next to it. But, each row has different data, so I can't say 'Find ____ and Replace' because ________ is different each row. Normally, I would use Left, Right, or Mid, but the data is not standard each row.
For example, one row, cell B2 has 'Navy Front to Back Tank' in the cell and right next to it in C2 is 'Navy'. Since 'Navy' is part of B2 and in C2, Navy should be removed from B2.
On the next row, B3 has 'Optic White Birdseye Blouse' and C3 has 'Optic White'. Since Optic White is in B3 and C3, it needs to be found/removed from B3.
Thank you so much in advance for any help you could provide. I have been struggling with this one.
Related:
Help with Formula to Find/Remove if same data in another cell
Try the following macro code to see if it yields the desired result:
Sub RunMe()
Dim lRow As Integer
lRow = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range("B2:B" & lRow)
cell.Replace What:=cell.Offset(0, 1), Replacement:=vbNullString, LookAt:=xlPart
cell.Value = Trim(cell)
Next cell
End Sub
How to implement and run a code:
- From Excel hit Alt + F11 to open the "Microsoft Visual Basic" window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro's.
- Double-click the macro you wish to run.
NOTE: macro's cannot be reversed using the blue arrows. Always make sure you save your file before running a code, so you can reopen your file if something unforeseen happens or you want to go back to the situation before the code was run.
