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

 

[Rick Rothstein]

You will note that both my function and Rick's fail for column E but mine works for column F.

Why do you thing 67 should be returned by either ="58 YYY67" or ="58 YYYY67"? To me, the 67 is not a stand alone number as it is attached to the multiple Y's. Of course, it is up to the OP do decide but my interpretation of what the OP would want would exclude those 67's.

 

[Rick Rothstein]

If B1 has a formula with absolute addresses like this: =$B$20+2

Good catch! Here is the fix allowing it to handle this case...

Public Function ExtractNumber(Cell As Range) As String
  Dim x As Long, Txt As String, Arr As Variant
  Txt = Cell.Formula
  For x = 1 To Len(Txt)
    If Mid(Txt, x, 3) Like "[A-Z]$[0-9]" Then Txt = Application.Replace(Txt, x + 1, 1, "")
    If Mid(Txt, x, 1) Like "[!0-9A-Z.]" Then Mid(Txt, x) = " "
  Next
  Arr = Split(Application.Trim(Txt))
  For x = 0 To UBound(Arr)
    If Arr(x) Like "*[!0-9.]*" Or Arr(x) Like "*.*.*" Then Arr(x) = ""
  Next
  ExtractNumber = Replace(Application.Trim(Join(Arr)), " ", ", ")
End Function

 

[Peter_SSs]

If B1 has a formula with absolute addresses like this: =$B$20+2

My function already handles that.

Why do you thing 67 should be returned by either ="58 YYY67" or ="58 YYYY67"?

Only that those numbers are not part of cell references and we were told:

(excluding numbers that are part of cell references)

 

[Peter_SSs]

I have tweaked my function to pick up one other circumstance where it was definitely returning the wrong result (F1 below), and I wonder what is required if the formula in J1 is possible for the OP?

Function GetNums(c As Range) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([^A-Z0-9]\$?[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

ChristineJ.xlsm
	F	G			J
1	58YYY67	58 YYYY67			2.365E+45
2
3	58, 67	58, 67			2.365, 45, 6
Sheet1

Cell Formulas
Range	Formula
F1	F1	="58YYY67"
G1	G1	="58 YYYY67"
J1	J1	=2.365E+45+6
F3:G3,J3	F3	=GetNums(F1)

 

[Rick Rothstein]

Only that those numbers are not part of cell references and we were told:

You may be right (the OP's wording does say that) but my gut tells me the OP would not want 4 to be returned from "looking4you" and "yyyy67" is just an extension of this. I guess well have to wait until the OP clarifies this.

 

[Peter_SSs]

my gut tells me the OP would not want 4 to be returned from "looking4you"

You are quite possibly correct.

I guess well have to wait until the OP to clarifies this.

Agreed.

 

[ChristineJ]

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

Yes, thank you so much for all of this insight. My use of this formula is relatively contained, so it is doubtful I will encounter issues, but it is very educational to see how you analyze this. I do note that your code does work if there is a $ in the reference, which I did not yet realize was an issue with the previous solution. Thanks so much! I learned quite a bit from this.

 

[ChristineJ]

If B1 has a formula with absolute addresses like this: =$B$20+2

Thanks for this - you are right!

 

[ChristineJ]

You are quite possibly correct.



Agreed.

You're right about that - would not want numbers in a text string to be included! However, for my particular application, the possible text strings that can be used are predefined (I am teaching accounting, and my users can only use account names I give them). None of the account names I allow have numerical characters. However, great point in terms of what could go wrong.

 

[ChristineJ]

Good catch! Here is the fix allowing it to handle this case...

Public Function ExtractNumber(Cell As Range) As String
  Dim x As Long, Txt As String, Arr As Variant
  Txt = Cell.Formula
  For x = 1 To Len(Txt)
    If Mid(Txt, x, 3) Like "[A-Z]$[0-9]" Then Txt = Application.Replace(Txt, x + 1, 1, "")
    If Mid(Txt, x, 1) Like "[!0-9A-Z.]" Then Mid(Txt, x) = " "
  Next
  Arr = Split(Application.Trim(Txt))
  For x = 0 To UBound(Arr)
    If Arr(x) Like "*[!0-9.]*" Or Arr(x) Like "*.*.*" Then Arr(x) = ""
  Next
  ExtractNumber = Replace(Application.Trim(Join(Arr)), " ", ", ")
End Function

Very awesome! Thanks for the update. Takes care of the $ and works perfectly.