Filter numbers from words macro

[bookworm121]

Is there anyway using a macro you can go through all the cells of a column and filter the numbers with a commma seperating them?

Lets say column A has 20 cells (rows), the cell contents look something like this:

201325 ABC, 30593 DEF

The macro also needs to be able to take on any number of rows. So it will have to count how many rows are in the column first.

Thanks!

 

[Marcelo Branco]

Hi,

Maybe this UDF (you have to enable Micrososoft VBScript Regular Expressions 5.5)

Function FilterNumbers(t As String) As String
    'Go to Tools, References and check
    'Microsoft VBScript Regular Expressions 5.5
    Dim re As New RegExp
    Dim Matches As MatchCollection, StrRst As String
    Dim i As Long
 
    With re
        .Pattern = "\b\d+\b"
        .Global = True
        Set Matches = .Execute(t)
    End With
 
    If Matches.Count = 0 Then Exit Function
 
    For i = 0 To Matches.Count - 1
        StrRst = StrRst & Matches(i) & ", "
    Next i
 
    FilterNumbers = Left(StrRst, Len(StrRst) - 2)
 
End Function

A B
<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=291><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7277" width=199><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 149pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=199>201325 ABC, 30593 DEF


</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2763529 class=xl64 width=92>201325, 30593</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>12 XYZ, 1 BBB, 123456 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>12, 1, 123456</TD></TR></TBODY></TABLE>

Formula in B1
=FilterNumbers(A1)
copy down

HTH

M.

 

[bookworm121]

I'm using Excel 2007. Where do I find Micrososoft VBScript Regular Expressions 5.5 on there?

Hi,

Maybe this UDF (you have to enable Micrososoft VBScript Regular Expressions 5.5)

Function FilterNumbers(t As String) As String
    'Go to Tools, References and check
    'Microsoft VBScript Regular Expressions 5.5
    Dim re As New RegExp
    Dim Matches As MatchCollection, StrRst As String
    Dim i As Long
 
    With re
        .Pattern = "\b\d+\b"
        .Global = True
        Set Matches = .Execute(t)
    End With
 
    If Matches.Count = 0 Then Exit Function
 
    For i = 0 To Matches.Count - 1
        StrRst = StrRst & Matches(i) & ", "
    Next i
 
    FilterNumbers = Left(StrRst, Len(StrRst) - 2)
 
End Function

A B
<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=291 border=0><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7277" width=199><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 149pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=199 height=20>201325 ABC, 30593 DEF



</TD><TD class=xl64 id=td_post_2763529 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 69pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=92>201325, 30593</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>12 XYZ, 1 BBB, 123456 </TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12, 1, 123456</TD></TR></TBODY></TABLE>

Formula in B1
=FilterNumbers(A1)
copy down

HTH

M.

 

[Marcelo Branco]

I'm using Excel 2007. Where do I find Micrososoft VBScript Regular Expressions 5.5 on there?

Alt+F11 to open the VBEditor
Tools>References and look for Microsoft VBScript Regular Expressions 5.5
Ckeck it

Then
Insert>Module
and paste the UDF in the right-panel

M.

 

[bookworm121]

honestly, i posted the question thinking "i'll give this a shot.. i doubt excel of capable of automating this..." lo and behold it works...

I added more code in my macro (a seperate Sub) that basically puts the formula in all rows of A until the end of the spreadsheet (indicated by another column).. n yea it works fantastic!

Thanks

Alt+F11 to open the VBEditor
Tools>References and look for Microsoft VBScript Regular Expressions 5.5
Ckeck it

Then
Insert>Module
and paste the UDF in the right-panel

M.

 

[Marcelo Branco]

honestly, i posted the question thinking "i'll give this a shot.. i doubt excel of capable of automating this..." lo and behold it works...

I added more code in my macro (a seperate Sub) that basically puts the formula in all rows of A until the end of the spreadsheet (indicated by another column).. n yea it works fantastic!

Thanks

Yeah, Excel is amazing!!!

Take a look at
http://www.aivosto.com/vbtips/regex.html

You are welcome and tks for the feedback

M.