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 |
[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]
You may want to read my mini-blog article here...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]
Give this macro a try...
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.
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
If Not Cell.Value Like "*[!0-9]*" Then IDs = IDs & "," & Cell.Value
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.I like the way you handle the commas! I suppose the mid(IDs,2) gets rid of the 1st preceding comma?
It's a double negative (making a positive). If you tried to test it this way...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.
If Text Like "*[0-9]*" Then
If Text Like "*[!0-9]*" Then
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