Passing formula in cell

K

khalberg

New member
Joined
May 20, 2017
Messages
2
Reaction score
0
Points
0
I want to examine a cell which has a set of numbers added up in it and pick out the numbers. Example 12+24+45+76. I want to be able to get at the 12, 24, etc.
 
Welcome to the forum!

You could use the built in text to columns feature on the data ribbon using the + sign as the delimiter.
 
I think text to columns wants text in the cell. I have a formula. I should have indicated =12+24+45+76. I wonder if I could first convert the cell to a cell containing text and then break it apart?
 
If you have Excel 2013 or later, you can use FORMULATEXT() to convert the formula to text and separate the values.

e.g. if your original formula is in A2, try:

=IFERROR(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE(FORMULATEXT($A2),"=",""),"+",REPT(" ",100)),1+(100*(COLUMNS($A$1:A$1)-1)),100)),"")

copied across as far as necessary to extract the values between + signs.

You can copy these down for other formulas in column A.
 
If you don't have Excel 2013 or later you can make your own (very basic) FormulaText function and use it in just the same way as NBVC suggests.
Code: 
Function FormulaText(yyy)
FormulaText = yyy.Formula
End Function
 
You must log in or register to reply here.
Back
Top