Removing charecters in a cell

[lprabhu1107]

Hi All,

I have following in A1 (Sheet 1),

CELL A1: BOM XF DEL XOWIN1 END IXJ MOWIN1 INR DEL NUC BOM

Similar type of data is there from A1 to A2456.

From the above i have to extract only the following in B1

BOM DEL IXJ DEL BOM.

The charecters that needs to be removed are in the next worksheet as follows,

Sheet 2:

A1: XF
A2: XOWIN1
A3: END
A4: MOWIN1
A5: INR
A6: NUC
and so on till A 250.

In sheet1 B1, i need a macro/formula to check the sheet2 charecters and remove the same in A1 and display in B1

Is there a way to do this. Currently iam doing this manually and it takes lot of my time.

Pls help

 

[hiker95]

lprabhu1107,


Sample worksheets:

Excel Workbook
	A	B
1	BOM XF DEL XOWIN1 END IXJ MOWIN1 INR DEL NUC BOM
2	BOM XF DEL XOWIN1
3	XOWIN1 END IXJ MOWIN1 INR
4	MOWIN1 INR DEL NUC BOM
5
Sheet1

Excel Workbook
	A
1	XF
2	XOWIN1
3	END
4	MOWIN1
5	INR
6	NUC
7
Sheet2

After the macro:

Excel Workbook
	A	B
1	BOM XF DEL XOWIN1 END IXJ MOWIN1 INR DEL NUC BOM	BOM DEL IXJ DEL BOM
2	BOM XF DEL XOWIN1	BOM DEL
3	XOWIN1 END IXJ MOWIN1 INR	IXJ
4	MOWIN1 INR DEL NUC BOM	DEL BOM
5
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).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RemoveStrings()
' hiker95, 07/23/2011
' http://www.mrexcel.com/forum/showthread.php?t=566432
Dim A, S, b As Long, c As Long, f As Long, Sp, H As String
A = Worksheets("Sheet1").Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row)
S = Worksheets("Sheet2").Range("A1:A250")
For b = LBound(A) To UBound(A)
  Sp = Split(A(b, 1), " ")
  H = ""
  For c = LBound(Sp) To UBound(Sp)
    f = 0
    On Error Resume Next
    f = Application.Match(Sp(c), S, 0)
    On Error GoTo 0
    If f = 0 Then
      H = H & Sp(c) & " "
    End If
  Next c
  If H <> "" Then A(b, 2) = Left(H, Len(H) - 1)
Next b
Worksheets("Sheet1").Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row) = A
End Sub

Then run the RemoveStrings macro.

 

[lprabhu1107]

Hi Hiker,

It was fabulously working...you have saved my hours and hours of work..thank you very much

Prabhu

 

[hiker95]

lprabhu1107,

Thanks for the feedback.

You are very welcome.

Glad I could help.

Come back anytime.

For my own personal information: How much time in hours do you think my macro will save you per day/week/month?

 

[lprabhu1107]

i guess 5-6 hours a month...

 

[lprabhu1107]

Hi Hiker,

I have situation where the data in CELL A1 is as follows,

CELL A1: BOM XFDELXOWIN1ENDIXJ.MOWIN1.INR.DEL.NUC.BOM

that is with no space in between or with a full stop.

Can u pls assist with the above scenario

Regards

Prabhu

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>BOM XF DEL XOWIN1 END IXJ MOWIN1 INR DEL NUC BOM</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>BOM XF DEL XOWIN1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>XOWIN1 END IXJ MOWIN1 INR</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>MOWIN1 INR DEL NUC BOM</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>BOM XF DEL XOWIN1 END IXJ MOWIN1 INR DEL NUC BOM</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>BOM XF DEL XOWIN1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>XOWIN1 END IXJ MOWIN1 INR</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>MOWIN1 INR DEL NUC BOM</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>BOM XF DEL XOWIN1 END IXJ MOWIN1 INR DEL NUC BOM</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>BOM XF DEL XOWIN1</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>XOWIN1 END IXJ MOWIN1 INR</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>MOWIN1 INR DEL NUC BOM</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

 

[hiker95]

lprabhu1107,

I can change this:
BOM XFDELXOWIN1ENDIXJ.MOWIN1.INR.DEL.NUC.BOM

to this:
BOM XFDELXOWIN1ENDIXJ MOWIN1 INR DEL NUC BOM

But, I am not sure how to identify/split/separate and work with this:
XFDELXOWIN1ENDIXJ

We could do a search of each item in Sheet2 against XFDELXOWIN1ENDIXJ, and remove those that match, but we would be left with a joined string that is not separated.

You may want to create a NEW Post with the new variable strings in column A. If you do, then you may also want to put in your NEW Post a link to this Post.