Extract Numeric data and seperte with a comma

[vgolla]

ID
1254
4521
4021
IQID
4587
1251
125

<tbody>
</tbody>

I need a Macro that will extract only numeric data into a cell like ::: (1254,4521,4021,4587,1251,125)
with parenthesis and separated by commas.

Thanks for help.

 

[mrmmickle1]

Excel 2013
	A	B
1	ID	Result
2	1,254	(1254, 4521, 4021, 4587, 1251, 125)
3	4521
4	4021
5	IQID
6	4587
7	1251
8	125
Sheet1

Try this:

[COLOR=#0000ff]Sub [/COLOR]Test()


    [COLOR=#0000ff]For[/COLOR] i = 2 [COLOR=#0000ff]To[/COLOR] 8
        [COLOR=#0000ff]If[/COLOR] IsNumeric(Cells(i, 1)) [COLOR=#0000ff]Then[/COLOR]
            [COLOR=#0000ff]If[/COLOR] i <> 8 [COLOR=#0000ff]Then[/COLOR]
                NewString = NewString & Cells(i, 1) & ", "
[COLOR=#0000ff]            Else[/COLOR]
                NewString = NewString & Cells(i, 1)
[COLOR=#0000ff]            End If[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next [/COLOR][COLOR=#000000]i[/COLOR]
    Range("B2") = "(" & NewString & ")"
[COLOR=#0000ff]End Sub[/COLOR]

 

[Rick Rothstein]

Try this:

[COLOR=#0000ff]Sub [/COLOR]Test()


    [COLOR=#0000ff]For[/COLOR] i = 2 [COLOR=#0000ff]To[/COLOR] 8
        [COLOR=#0000ff]If[/COLOR] [B][COLOR=#FF0000]IsNumeric[/COLOR][/B](Cells(i, 1)) [COLOR=#0000ff]Then[/COLOR]
            [COLOR=#0000ff]If[/COLOR] i <> 8 [COLOR=#0000ff]Then[/COLOR]
                NewString = NewString & Cells(i, 1) & ", "
[COLOR=#0000ff]            Else[/COLOR]
                NewString = NewString & Cells(i, 1)
[COLOR=#0000ff]            End If[/COLOR]
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Next [/COLOR][COLOR=#000000]i[/COLOR]
    Range("B2") = "(" & NewString & ")"
[COLOR=#0000ff]End Sub[/COLOR]

You may want to read my mini-blog article here...

<!-- title / author block --> [h=3]Thinking About Using VBA's IsNumeric Function? Read this first[/h]

 

[Rick Rothstein]

ID
1254
4521
4021
IQID
4587
1251
125

<tbody>
</tbody>

I need a Macro that will extract only numeric data into a cell like ::: (1254,4521,4021,4587,1251,125)
with parenthesis and separated by commas.

Give this macro a try...

Sub GetNumericIDs()
  Dim Cell As Range, IDs As String
  For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants, xlNumbers)
    If Not Cell.Value Like "*[!0-9]*" Then IDs = IDs & "," & Cell.Value
  Next
  Range("B2").Value = "'(" & Mid(IDs, 2) & ")"
End Sub

 

[mrmmickle1]

Thanks Rick

I just took a gander at your blog article. I didn't realize all of the flaws.

I like the way you handle the commas! I suppose the mid(IDs,2) gets rid of the 1st preceding comma?

The one thing I didn't quite comprehend was this line:

If Not Cell.Value Like "*[!0-9]*" Then IDs = IDs & "," & Cell.Value

Is this saying if the cell value does not resemble numeric characters? I would think that this would return the opposite of what you want.

However, I tested it and of course it works! (Not really a surprise )


So my question is how is it that this line of code operates the way it does? It seems counter intuitive.

 

[Rick Rothstein]

I like the way you handle the commas! I suppose the mid(IDs,2) gets rid of the 1st preceding comma?

Yes, I find it easier to do put the commas in first because mid is so simple to implement for removing the first character (especially since its third argument is optional defaulting to the remainder of the text when omitted)... putting the comma in last requires a combination of Left and Len (minus the length of the delimiter) and is more awkward in my opinion.

The one thing I didn't quite comprehend was this line:

If Not Cell.Value Like "*[!0-9]*" Then IDs = IDs & "," & Cell.Value

Is this saying if the cell value does not resemble numeric characters? I would think that this would return the opposite of what you want.

However, I tested it and of course it works! (Not really a surprise )

So my question is how is it that this line of code operates the way it does? It seems counter intuitive.

It's a double negative (making a positive). If you tried to test it this way...

If Text Like "*[0-9]*" Then

all that would tell you is that at least one digit was in the text... it would say nothing about the other characters. Doing this...

If Text Like "*[!0-9]*" Then

would evaluate as true if at least one character was not a digit (that is what the exclamation point does... matches all characters not in the list), but that is not what you want the Then block to execute for... you want it to execute if none of the characters in Text are non-digits, to you apply the Not operator to get that as the True result.

 

[vgolla]

Thanks sir (Rick)

 

[mikerickson]

Anther option uses the ConcatIf UDF

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

with the formula =ConcatIf(A1:A10, ">0")

 

[mrmmickle1]

Rick,

I believe I understand now. I thought it may have to do with something like a double negative.... but I was unable to see the second negative (!). Therefore, I was having trouble comprehending the solution. Thanks for the very thorough explanation! I appreciate you taking the time to explain the solution