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

 

[Peter_SSs]

would not want numbers in a text string to be included!

In that case I would change my first Pattern line ..

Function GetNums(c As Range) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([A-Z]\$?[0-9]+)|(" & Chr(34) & ".*?" & Chr(34) & ")"
    GetNums = .Replace(c.Formula, "x")
    .Pattern = "[^\d\.]"
    GetNums = Replace(Application.Trim(.Replace(GetNums, " ")), " ", ", ")
  End With
End Function

ChristineJ.xlsm
	A	B	C	D	E	F	G	H
1		-5	-1 Cash	108	Peter 58 YYYY-67	15629.6	2.4	2.365E+45
2
3	Peter	5	6, 7	75, 12, 108		4.6, 5, 6	2.4	2.365, 45, 6
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1-5
C1	C1	=A1+6-B2-7&" Cash"
D1	D1	=(SUM(D16:D18)-G219-75)/(D20*12)*DATEDIF(C15,F21,"m")+108
E1	E1	=A3&" 58 YYYY-67"
F1	F1	=4.6+A1+5^6
G1	G1	=$B$20+2.4
H1	H1	=2.365E+45+6
B3:H3	B3	=GetNums(B1)

 

[Rick Rothstein]

@Peter,

Your last example sheet raises some questions. Probably the OP's data will never see the following constructions but if the OP can have them, then I might have to modify my code... and you also for one of them. First is this one...

=A3&" 58 YYYY-67"

I return both the 58 and 67, but I wonder about that 67. It is kind of attached to the YYYY via the dash so I am thinking I should not have returned it. Your code, on the other hand, does not return the 67 but you also do not return the 58 which I think should definitely be returned. Of course, if the dash is a connector, then so would two dashes, an underline, as well as other non-letter characters without surrounding spaces all of which my code would handle incorrectly. Then other one is...

=2.365E+45+6

Here you return three numbers... 2.365, 45 and 6 whereas I return just 45 and 6 because of the attached E. But 2.365E+45 as a whole is a number... should it be returned in its entirety? If not, is your handling of the formula or mine the correct way to go?

Again, if either of these are constructions the OP can have, the we will need a clarification post for them.

 

[Peter_SSs]

I return both the 58 and 67, but I wonder about that 67. It is kind of attached to the YYYY via the dash so I am thinking I should not have returned it. Your code, on the other hand, does not return the 67 but you also do not return the 58 which I think should definitely be returned.

Post #19 says to exclude numbers in a text string - which both 58 and 67 are.

Here you return three numbers... 2.365, 45 and 6 whereas I return just 45 and 6 because of the attached E. But 2.365E+45 as a whole is a number... should it be returned in its entirety? If not, is your handling of the formula or mine the correct way to go?

Exactly why I asked in post #14:

I wonder what is required if the formula in J1 is possible for the OP?

 

[Rick Rothstein]

Post #19 says to exclude numbers in a text string - which both 58 and 67 are.

I don't think the OP meant quoted text when he wrote that, especially since he followed it up talking about not having numbers in account names. I read his use of "text strings" there as meaning specifically letters with numbers embedded within or attached to them.

 

[ChristineJ]

This functions you provided have been very useful.

I thought of another use for this functionality and was wondering if it could be adapted so that it is a macro for cells in a range rather than a function.

For example, when the macro runs, it looks at the cells in range A1:A5 (which have formulas in them) and in the corresponding row in column B (B1:B5) returns the numbers in the formula that are not part of a cell reference (as the functions already do).

Thanks, as always!

 

[Peter_SSs]

For example, when the macro runs, it looks at the cells in range A1:A5 (which have formulas in them) and in the corresponding row in column B (B1:B5) returns the numbers in the formula that are not part of a cell reference (as the functions already do).

For my function from post #21, the conversion would be this.

Sub Get_Nums()
  Dim a As Variant
  Dim RX As Object
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  a = Range("A1:A5").Formula
  For i = 1 To UBound(a)
    RX.Pattern = "([A-Z]\$?[0-9]+)|(" & Chr(34) & ".*?" & Chr(34) & ")"
    a(i, 1) = RX.Replace(a(i, 1), "x")
    RX.Pattern = "[^\d\.]"
    a(i, 1) = Replace(Application.Trim(RX.Replace(a(i, 1), " ")), " ", ", ")
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

 

[ChristineJ]

For my function from post #21, the conversion would be this.

Sub Get_Nums()
  Dim a As Variant
  Dim RX As Object
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  a = Range("A1:A5").Formula
  For i = 1 To UBound(a)
    RX.Pattern = "([A-Z]\$?[0-9]+)|(" & Chr(34) & ".*?" & Chr(34) & ")"
    a(i, 1) = RX.Replace(a(i, 1), "x")
    RX.Pattern = "[^\d\.]"
    a(i, 1) = Replace(Application.Trim(RX.Replace(a(i, 1), " ")), " ", ", ")
  Next i
  Range("B1").Resize(UBound(a)).Value = a
End Sub

Absolutely perfect! Thank you so much for the help.

 

[Peter_SSs]

Absolutely perfect! Thank you so much for the help.

You're welcome. Glad to help.

 

[ChristineJ]

I have been using the following function provided in this thread:

Function GetNums(c As Range) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([^A-Z]\$?[A-Z]{1,3}\$?[0-9]{1,7})"
    GetNums = .Replace(c.Formula, "x")
    .Pattern = "[^\d\.]"
    GetNums = Replace(Application.Trim(.Replace(GetNums, " ")), " ", ", ")
  End With
End Function

Is it possible to convert this code to a macro that looks at column G? Each cell in column G is either blank or has a cell reference typed in. For example, cell G51 contains the value C24. The macro should operate like the INDIRECT function and look at the formula in C24. It should than apply this "GetNums process" to return the number(s) in this formula to cell T51. So if the value in G51 is C24 and the formula in C24 is =A1+100-K15-6, T51 would show 100, 6.

This would repeat for each cell in column G that has a cell reference, and any numbers in the formula would be returned in column T on the same row as the entry in column G.

Hope this is clear, and thanks.

 

[Peter_SSs]

Give this a try

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