Remove Charecters/Numbers in cell

[lprabhu1107]

Hi Experts

I have following in Column A

<TABLE style="WIDTH: 605pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=806><COLGROUP><COL style="WIDTH: 605pt; mso-width-source: userset; mso-width-alt: 29476" width=806><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 605pt; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20 width=806>MAA AI X/BOM AI JFK550.19XRAOPIN1/PIN4 AI X/MNL AI SIN550.19XRAOPIN1/PIN4 NUC1100.38 XF JFKINR4.5END ROE44.976</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>MAA AI X/DXB AI LAX550.19XRAOPIN1/PIN4 AI X/DXB AI MAA550.19XRAOPIN1/PIN4 NUC1100.38 XF LAXINR4.5END ROE44.976</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>MAA AI X/DXB AI LAX550.19XRAOPIN1/PIN4 AI X/DXB AI MAA550.19XRAOPIN1/PIN4 NUC1100.38 XF LAXINR4.5END ROE44.976</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>MAA AI X/DXB AI LAX550.19XRAOPIN1/PIN4 AI X/DXB AI MAA550.19XRAOPIN1/PIN4 NUC1100.38 XF LAXINR4.5END ROE44.976</TD></TR></TBODY></TABLE>

After using a macro i remove the numbers from the each cell. Then the columns in A1 is as follows,

<TABLE style="WIDTH: 605pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=806><COLGROUP><COL style="WIDTH: 605pt; mso-width-source: userset; mso-width-alt: 29476" width=806><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 605pt; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20 width=806>MAA AI X/BOM AI JFK.XRAOPIN/PIN AI X/MNL AI SIN.XRAOPIN/PIN NUC. XF JFKINR.END ROE.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>MAA AI X/DXB AI LAX.XRAOPIN/PIN AI X/DXB AI MAA.XRAOPIN/PIN NUC. XF LAXINR.END ROE.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>MAA AI X/DXB AI LAX.XRAOPIN/PIN AI X/DXB AI MAA.XRAOPIN/PIN NUC. XF LAXINR.END ROE.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>MAA AI X/DXB AI LAX.XRAOPIN/PIN AI X/DXB AI MAA.XRAOPIN/PIN NUC. XF LAXINR.END ROE.</TD></TR></TBODY></TABLE>

from the above column A i need to remove certain charecters and the final output should be as follows,

Cell A1: MAA BOM JFK MNL SIN JFK
Cell A2: MAA DXB LAX DXB MAA LAX
Cell A3: MAA DXB LAX DXB MAA LAX
Cell A4: MAA DXB LAX DXB MAA LAX

Charecters to be removed is placed in Sheet 2 Column A as follows,

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20 width=64>AI</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>X/</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>XRAOPIN/PIN</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>NUC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>XF</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>INR</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>END</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=20>ROE</TD></TR></TBODY></TABLE>

Is this possible....

 

[Marcelo Branco]

Maybe this UDF

Copy the function below to a standard module

Function test(t As String)
'Remove
'AI X/ XRAOPIN/PIN NUC XF . INR End ROE
'from string
 
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "AI|X\/|XRAOPIN\/PIN|NUC|XF|\.|INR|End|ROE"
        test = .Replace(t, "")
    End With
 
End Function

Usage
Assuming the strings in A1, A2, ...

B1
=TRIM(test(A1))

copy down

HTH

M.

 

[lprabhu1107]

Hi,

This works fine. but i have a issue here.

The charecters to be removed varies from Cell to Cell. hence i have placed the same in sheet 2 which runs in to 100 cells.

instead of hard coding the charecters to be removed in the macro, can it be made to pick from sheet 2 colum A

Regards

Prabhu

 

[Marcelo Branco]

I think you can create a string to format the pattern, but taking care that special characters need to be precedeed by \ (backslash) like in

.Pattern = "AI|X\/|XRAOPIN\/PIN|NUC|XF|\.|INR|End|ROE"

So if the characters to be removed are, for example, in range X1:X10, first include the backslash(es) where they are necessaries, then you can build a string something like (NOT TESTED)

Dim myStr as String, aCell as Range
 
For each aCell in Range("X1:X10")
    myStr = myStr & "|" & aCell.Text
Next aCell

and use myStr as the pattern

M.

 

[Marcelo Branco]

I think a better solution is to change the function to accept the pattern as a second parameter

Like

Function test(t As String, pat As String)
'Remove pat
'from string
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = pat
        test = .Replace(t, "")
    End With
 
End Function

 

[lprabhu1107]

Hi,

If i use this code, it gives an #name error

 

[Marcelo Branco]

Did you delete the previous version?

Just in case, try

Function test3(t As String, pat As String)
'Remove pat
'from string
 
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = pat
        test3 = .Replace(t, "")
    End With
    
End Function

And in B1 something like

=TRIM(Test3(A1,"AI|X\/|XRAOPIN\/PIN|NUC|XF|\.|INR|End|ROE"))

M.