VBA to extract only number from a cells in Excel report

[smide]

Hello.
I need some assistance with an excel spreadsheet which contains very raw output report in row 1 columns (A-ZW).

In row1 in Sheet2 I have a raw report output which contains certain Products id's.
The problem is that I need to find cells that contain this data about product's id's, then to extract only id numbers from those cells without unnecessary characters in those cells.

There are four type of cells which contain product's id characteristics (all cells are in row 1, Sheet2 as I said):

Example:

1) events:[{"id":45 - I need only number 45 from this cell

2) {"id":626702572 - I need only number 626702572

but there are also "dummy" cells with similar "id" characteristics:

3) account:[{"id":2370

or

4) annual:[{"id":3460

I do not need id from these cells !! (from 3) and/or 4) )

Extended example

Sheet2 (raw report)[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]events:[{"id":[/TD]
[TD]status:"I" [/TD]
[TD]events:[{"id":7650
[/TD]
[TD]starts:[/TD]
[TD]maxTotal:50 [/TD]
[TD]{"id":976[/TD]
[TD]account:[{"id":2370[/TD]
[TD]cutoff:[/TD]
[TD]{"id":45700[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
* deliberately I skipped id from G1(!) cell because I do not need id with this characteristics (case 3) )

Sheet1 (after macro or formula run)

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]7650[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]976[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]45700[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"].....[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

See if this macro does what you want...

Sub GetIDs()
  Dim C As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(1, Columns.Count).End(xlToLeft))
  ReDim Result(1 To UBound(Data, 2), 1 To 1)
  For C = 1 To UBound(Data, 2)
    If Data(1, C) Like "*""[Ii][Dd]"":#*" And _
       Left(LCase(Data(1, C)), 14) <> "annual:[{""id"":" And _
       Left(LCase(Data(1, C)), 15) <> "account:[{""id"":" Then
      X = X + 1
      Result(X, 1) = Mid(Data(1, C), InStrRev(Data(1, C), ":") + 1)
    End If
  Next
  Range("A2").Resize(UBound(Result)) = Result
End Sub

 

[smide]

Thank you for reply.
Unfortunately there is a some problem with code:
Run-time error '13':
Type mismatch

Some problem in (After debug):
ReDim Result(1 To UBound(Data, 2), 1 To 1)

 

[Rick Rothstein]

Thank you for reply.
Unfortunately there is a some problem with code:
Run-time error '13':
Type mismatch

Some problem in (After debug):
ReDim Result(1 To UBound(Data, 2), 1 To 1)

Was Sheet2 active when you ran my code? If no, try it again after making Sheet2 active... if yes, then is your data really on Row 1?

 

[smide]

Was Sheet2 active when you ran my code? If no, try it again after making Sheet2 active... if yes, then is your data really on Row 1?

I see now...
I run code (again) from Sheet2 (active sheet) and results were in column A in Sheet2, but Sheet1 was still empty...
How can I transfer results in Sheet1 (column A) directly ?
Basically I want to run code from Sheet1 and to get results also in Sheet1...

 

[Rick Rothstein]

Basically I want to run code from Sheet1 and to get results also in Sheet1...

I have modified my code so you can run it from any sheet...

[table="width: 500"]
[tr]
	[td]Sub GetIDs()
  Dim C As Long, X As Long, Data As Variant, Result As Variant
  Data = Sheets("Sheet2").Range("A1", Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft))
  ReDim Result(1 To UBound(Data, 2), 1 To 1)
  For C = 1 To UBound(Data, 2)
    If Data(1, C) Like "*""[Ii][Dd]"":#*" And _
       Left(LCase(Data(1, C)), 14) <> "annual:[{""id"":" And _
       Left(LCase(Data(1, C)), 15) <> "account:[{""id"":" Then
      X = X + 1
      Result(X, 1) = Mid(Data(1, C), InStrRev(Data(1, C), ":") + 1)
    End If
  Next
  Sheets("Sheet1").Range("A2").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

 

[smide]

I have modified my code so you can run it from any sheet...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub GetIDs()
  Dim C As Long, X As Long, Data As Variant, Result As Variant
  Data = Sheets("Sheet2").Range("A1", Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft))
  ReDim Result(1 To UBound(Data, 2), 1 To 1)
  For C = 1 To UBound(Data, 2)
    If Data(1, C) Like "*""[Ii][Dd]"":#*" And _
       Left(LCase(Data(1, C)), 14) <> "annual:[{""id"":" And _
       Left(LCase(Data(1, C)), 15) <> "account:[{""id"":" Then
      X = X + 1
      Result(X, 1) = Mid(Data(1, C), InStrRev(Data(1, C), ":") + 1)
    End If
  Next
  Sheets("Sheet1").Range("A2").Resize(UBound(Result)) = Result
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Exactly what I was looking for, thank you so much.