Extracting & isolating multiple numerical values from a string

[awebmanager]

I have a list of 300 web URLs in a worksheet. Here are some typical examples:

/resources?keys=&tid_3=All&tid_5[]=50&tid_7=277

<colgroup><col width="487"></colgroup><tbody>
</tbody>

/resources?keys=lasagna&tid_3=35&tid_5[]=51&tid_7=277

<colgroup><col width="828"></colgroup><tbody>
</tbody>

/resources?keys=&tid_3=39&tid_5[]=50&tid_5[]=51&tid_5[]=54&tid_5[]=52&tid_5[]=53&tid_5[]=329&tid_7=277

<colgroup><col width="828"></colgroup><tbody>
</tbody>

The numbers after the = signs all refer to unique category terms which I have stored in a second worksheet which can be used as a lookup table. That worksheet looks like this:

Term ID	Term name
50	glass
277	england
39	fruit

<tbody>
</tbody>

I've got the lookup table part working OK, so that if in worksheet 1 I simply enter the number 50 in cell A1, Excel puts "glass" in cell A2, or if I had 277 in cell G1 it would put "england" in cell G2, and so on.

What I can't work out is a formula to automatically extract the numbers from the URLs and isolate those without all the other stuff. So for example in the URL /resources?keys=&tid_3=All&tid_5[]=50&tid_7=277 all I want are the numbers 50 and 277.

I don't mind if each of those numbers has to appear in a different column, although ideally I'd like the extracted data to show the multiple numbers in one cell e.g. "50, 277" so that the lookup function then returns "glass, england" in one cell adjacent to the URL cell.

This is probably all a long shot but I'd be grateful for any help.

Thanks

 

[Worf]

Hello and welcome to the Board

Is a VBA solution suitable for you?

 

[István Hirsch]

Try this UDF. Press Alt + F11, select Insert/Module and paste this code into an empty Module. Then go back to the sheet and select ExtrNum function from User Defined Functions.

Function ExtrNum(c As String) As String
    With CreateObject("VBScript.RegExp")
        c = c + "&"
        .Global = True
        .Pattern = "=(\d+)&|.+?"
        If .Test(c) Then ExtrNum = Application.Trim(.Replace(c, "$1 "))
    End With
End Function

 

[awebmanager]

Hey Istvan, thanks so much for your help! That did the trick ray:

Thanks also to Worf for the welcome. Great board!

 

[Rick Rothstein]

Try this UDF. Press Alt + F11, select Insert/Module and paste this code into an empty Module. Then go back to the sheet and select ExtrNum function from User Defined Functions.

Function ExtrNum(c As String) As String
    With CreateObject("VBScript.RegExp")
        c = c + "&"
        .Global = True
        .Pattern = "=(\d+)&|.+?"
        If .Test(c) Then ExtrNum = Application.Trim(.Replace(c, "$1 "))
    End With
End Function

For those who might be interested, here is a non-RegExp version of István's UDF...

Function ExtrNum(S As String) As String
  Dim X As Long, Nums() As String
  Nums = Split(S, "=")
  For X = 0 To UBound(Nums)
    If Nums(X) Like "#*" Then
      Nums(X) = Val(Nums(X))
    Else
      Nums(X) = ""
    End If
  Next
  ExtrNum = WorksheetFunction.Trim(Join(Nums))
End Function