Extract numbers from a string

[ChristineJ]

Is there a way to return only the numbers in a formula (excluding numbers that are part of cell references), with a comma after each?

=A1-5 would return 5,
=A1+6-B2-7&" Cash" would return 6,7,
=(SUM(D15:D17)-F218-75)/(D19*12)*DATEDIF(C14,E20,"m")+108 would return 75,12,108

 

[ChristineJ]

Yes, absolutely perfect! The original function was working well, but I thought of a more streamlined way of laying out my project. This is so helpful! As always, appreciate your assistance. C

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[ChristineJ]

I have been successfully using the following code from the previous post. It returns the numbers from each entry in column G to column T on the respective row. However, I have a separate situation where there is only ONE cell in column G that has a value.

For example, cell G8 contains this text entry: =D3+E15 - 12 + 70
It should return 12, 70 to T8

No other cells in column G have any values. The code below does not work. I assume that is because it is looking to go to the next row with a value, and there isn't one. Is there a way to tweak this code for this situation? Thanks!

Sub Get_Nums_v2()
  Dim a As Variant, b As Variant
  Dim RX As Object
  Dim i As Long
  Dim cell As Range
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  a = Range("G1", Range("G" & Rows.Count).End(xlUp)).Value2
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    Set cell = Nothing
    On Error Resume Next
    Set cell = Range(a(i, 1))
    On Error GoTo 0
    If Not cell Is Nothing Then
      If cell.HasFormula Then
        RX.Pattern = "([A-Z]\$?[0-9]+)|(" & Chr(34) & ".*?" & Chr(34) & ")"
        b(i, 1) = RX.Replace(cell.Formula, "x")
        RX.Pattern = "[^\d\.]"
        b(i, 1) = Replace(Application.Trim(RX.Replace(b(i, 1), " ")), " ", ", ")
      End If
    End If
  Next i
  Range("T1").Resize(UBound(b)).Value = b
End Sub

 

[Peter_SSs]

It is unclear to me.
G8 contains a formula?? The code is actually to get a cell reference from column G, not a formula, and go to the formula in whatever that cell reference is.
Further, if G8 (only) contains a cell reference the code works for me.

Where it fails for me is if if cell G1 only contains a cell reference.

Can you clarify the exact circumstances, code being used and what exactly is meant by "does not work"?

 

[ChristineJ]

Thanks for the reply, and apologies if I was unclear. By some miracle, I figured it out myself by tweaking the code. Appreciate it!

 

[Peter_SSs]

Glad you got it sorted. Thanks for letting us know.