Regular expression and Macro

[ankianand88]

I have an excel sheet which contains data in A column.and data is of the form
<TABLE style="WIDTH: 307pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=409 border=0><COLGROUP><COL style="WIDTH: 307pt; mso-width-source: userset; mso-width-alt: 14957" width=409><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; WIDTH: 307pt; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=409 height=20>Sub Total (SEPT MINOR 2011)</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; WIDTH: 307pt; BORDER-BOTTOM: gray 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=409 height=20>Sub Total (336103 NOV 11)</TD></TR></TBODY></TABLE>

i need a macro which gives the data in the following format
SEPT MINOR 2011
336103 NOV 11

some guys told me regex can be used..but i have no clue..how to go about it...please help

 

[Michael M]

Why not simply do a Find / Replace for both unwanted items

 

[ankianand88]

Actually there are many entries in the sheet...that was just an example....
The sheet consist of many entries like the two mentioned above

 

[Michael M]

My first answer still applies regardless of how many entries there are.
A simple recorded macro should do it

Sub Macro1()
    Columns("A:A").Replace What:="Sub Total", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows
    Columns("A:A").Replace What:="(", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows
    Columns("A:A").Replace What:=")", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows
End Sub

 

[ankianand88]

Hey thanks its working..but a little glitch...
like some rows contain numeric value like
Sub Total(361628)
and after running the macro am getting
-361628
A minus (-) sign is coming infront of numeric values only

 

[Michael M]

I can't see why this would happen
Have you tried recording a macro yourself. As you can see the replacement does not have any negative signs anywhere in it, it simply replaces with "", which is a blank.

 

[Biz]

Another variant macro

Sub test2()
Dim sString
Dim i As Long, a As Long, b As Long, c As Long
 
'Capture inputs
sString = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
 
For i = 1 To UBound(sString)
a = InStr(Trim(sString(i, 1)), "(") + 1
b = InStr(Trim(sString(i, 1)), ")") - 1
c = b - a
Cells(i, 1) = Mid$(Trim(sString(i, 1)), a, c + 1)
Next i
 
End Sub

Hope dis helps.

Biz

 

[mikerickson]

With Range("A:A")
    .TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="(", FieldInfo:=Array(Array(1, 9), Array(2, 1))
    On Error Resume Next
    .TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=")", FieldInfo:=Array(Array(1, 1), Array(2, 1))
    On Error GoTo 0
End With

 

[ankianand88]

Thanks! its working...just a typo error..its working fine