Extract Numeric data and seperte with a comma

vgolla

New Member
Joined
Dec 15, 2014
Messages
7
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">ID</td><td style=";">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1,254</td><td style=";">(1254, 4521, 4021, 4587, 1251, 125)</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">4521</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4021</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">IQID</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4587</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">1251</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">125</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

Try this:

Code:
[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]
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
Try this:

Code:
[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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
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...
Code:
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

Well-known Member
Joined
May 11, 2012
Messages
2,456

ADVERTISEMENT

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:

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,973
Office Version
  1. 2016
Platform
  1. Windows
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:

Code:
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...

Code:
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...

Code:
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

New Member
Joined
Dec 15, 2014
Messages
7

ADVERTISEMENT

Thanks sir (Rick)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,981
Anther option uses the ConcatIf UDF
Code:
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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,550
Messages
5,636,962
Members
416,952
Latest member
prakashkumar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top