VBA code to get an array of numeric values from a string

[gifariz]

As in post title, I need a VBA code (can be sub or function) to get an array of numeric values given a string input.

Suppose given a string variable: "H (120x150x250)x12.5x15/10",
desired output would be a double array with elements of 120, 150, 250, 12.5, 15, 10.

The string will have random format (unknown delimiter), so Split function can't be used.
I think hard but my brain can't find effective way to do this.
Any help guys? No need to be complete code, simply idea would be helpful.
TIA.

 

[Peter_SSs]

See if this is heading in the right direction (though I did use Split )

Sub StringToArray()
  Dim RX As Object
  Dim s As String
  Dim ary As Variant
  
  s = "H (120x150x250)x12.5x15/10"
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "[^\d.]"
  ary = Split(Application.Trim(RX.Replace(s, " ")))
End Sub

 

[Phuoc]

Try this code

Public Function ExtractNumber(ByVal s As String) 
Dim i As Long
For i = 1 To Len(s)
    If Mid(s, i, 1) Like "[0-9.]" = False Then Mid(s, i, 1) = " "
Next i
    ExtractNumber = Split(Application.Trim(s), " ")
End Function

 

[gifariz]

Thank you Peter & Phuoc. Sorry for my late reply.

Both of your code works (I just need to CDbl the result). I choose Phuoc because it has no extra object (I don't get used with object yet).
The idea of replacing unwanted character with delimiter would make me able to make a working code, but I also learnt many things from both of your answers:
- 'Like' in If
- Patterns
- applying Mid (and probably many other functions) on left side of the equal operator
- VBScript.RegExp library
I will explore more about this

Thank you all

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.