extract numbers from formula in excel

[littlepuppy]

Hey guys,

I want to extract the numbers out from the formula in excel.
For example,

30=9+8+7+6 the number in the cell is 30. The 30 comes from 4 different numbers. I want to get each of the number out separately, like 9, 8, 7, and 6. each of them separately.

8,450,668.83=38062098.32-29611347.24-82.25
Same as here. The cell number 8,450,668.83 comes from three different numbers. How to get 38062098.32, -29611347.24, -82.25 out and each of them separately.

Thank you

 

[XOR LX]

Hi.

Not sure I understand. What's actually in the cell? The formula:

=9+8+7+6

?

Regards

 

[Rick Rothstein]

If I understand your question correctly, give this macro a try...

Sub GetNumbersFromSimpleFormula()
  Dim Frmla As String, Cell As Range, Nums As Variant
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Frmla = Cell.Formula
    Nums = Split(Replace(Mid(Frmla, 2), "-", "+"), "+")
    With Cell.Offset(, 1).Resize(, UBound(Nums) + 1)
      .Value = Nums
      .Value = .Value
    End With
  Next
End Sub

 

[littlepuppy]

Hi.

Not sure I understand. What's actually in the cell? The formula:

=9+8+7+6

?

Regards

yes, the cell shows up 30. but you click the cell, the cell contents a formula which is "=9+8+7+6".