Remove values from a string of numbers

[ChristineJ]

Is there a formula for A3 to return a string of all numerical amounts in the formula in A1 EXCEPT those used to designate the number of decimals in ROUND functions?

FORMULA in cell A1 =ROUND(E15,0)+ROUND(E16,3)+SUM(E17,6)+0+ROUND(SUM(E21,10)+B29,0)+ROUND(E24-20,2)+3

All numbers in formula in A1, in sequence 0, 3, 6, 0, 10, 0, 20, 2, 3

RESULT in cell A3 should be 6, 0, 10, 20, 3
(only numbers not used to designate number of decimals in ROUND functions, separated by a comma and space)

 

[PeteWright]

Assuming that A1 has any kind of formula containing ROUND functions which can contain one nested function.
A little tedious, but does this work for you?

MrExcel_2025-03.xlsm
	A
1	-1
2
3	6, 0, 10, 20, 3
Sheet2

Cell Formulas
Range	Formula
A1	A1	=ROUND(E15,0)+ROUND(E16,3)+SUM(E17,6)+0+ROUND(SUM(E21,10)+B29,0)+ROUND(E24-20,2)+3
A3	A3	= LET( Repl1, FORMULATEXT(A1), Repl2, REGEXREPLACE(Repl1,"ROUND\(([a-z]+\([^()]+\)[^()]+|[^()]+[^()]+);\d\)", "\1",0,1), Repl3, REGEXREPLACE(Repl2, "[a-z]{1,3}\d{1,7}", "", 0, 1), Repl4, REGEXREPLACE(Repl3, "\D", "_", 0, 1), Repl5, REGEXREPLACE(Repl4, "_+(\d+)", "\1, ", 0, 1), Repl6, REGEXREPLACE(Repl5, ", $", "", 0, 1), Repl6 )

 

[ChristineJ]

Wow, interesting! Thanks for this incredible effort!

However, when I enter your LET formula in cell A3, it returns all of the standalone numbers: 0, 3, 6, 0, 10, 0, 20, 2, 3

This is unlike your (correct) result of 6, 0, 10, 20, 3

Any idea what my issue could be? I tested the REGREPLACE function in a different cell using =REGEXREPLACE("tuttle","t","b"), and it changed "tuttle" to "bubble" just fine.

Thanks - C

 

[PeteWright]

Yeah, missed that one...
It's because I have a semicolon as a list separator on my computer.
I changed all but one semicolon. Here's the correct formula:

= LET( Repl1, FORMULATEXT(A1), Repl2, REGEXREPLACE(Repl1,"ROUND\(([a-z]+\([^()]+\)[^()]+|[^()]+[^()]+),\d\)", "\1",0,1), Repl3, REGEXREPLACE(Repl2, "[a-z]{1,3}\d{1,7}", "", 0, 1), Repl4, REGEXREPLACE(Repl3, "\D", "_", 0, 1), Repl5, REGEXREPLACE(Repl4, "_+(\d+)", "\1, ", 0, 1), Repl6, REGEXREPLACE(Repl5, ", $", "", 0, 1), Repl6 )

 

[Peter_SSs]

Perhaps I have missed some circumstances, but building off the REGEX above, would this alternative in A5 do the same job?

25 03 07.xlsm
	A
1	-1
2
3
4	6, 0, 10, 20, 3
5	6, 0, 10, 20, 3
Extract Nums

Cell Formulas
Range	Formula
A1	A1	=ROUND(E15,0)+ROUND(E16,3)+SUM(E17,6)+0+ROUND(SUM(E21,10)+B29,0)+ROUND(E24-20,2)+3
A4	A4	= LET( Repl1, FORMULATEXT(A1), Repl2, REGEXREPLACE(Repl1,"ROUND\(([a-z]+\([^()]+\)[^()]+|[^()]+[^()]+),\d\)", "\1",0,1), Repl3, REGEXREPLACE(Repl2, "[a-z]{1,3}\d{1,7}", "", 0, 1), Repl4, REGEXREPLACE(Repl3, "\D", "_", 0, 1), Repl5, REGEXREPLACE(Repl4, "_+(\d+)", "\1, ", 0, 1), Repl6, REGEXREPLACE(Repl5, ", $", "", 0, 1), Repl6 )
A5	A5	=TEXTJOIN(", ",,REGEXEXTRACT(REGEXREPLACE(FORMULATEXT(A1),"ROUND\(([a-z]+\([^()]+\)[^()]+|[^()]+[^()]+),\d\)","\1",0,1),"(?<=[^A-Z\d])(\d+)",1,1))

 

[ChristineJ]

Thank you! Both of your versions are working as intended.

There is one thing I did not consider, and that is the decimals to round to may be a negative number, such as the two numbers in red below that I changed from the original formula. Is there a way to adapt so that those negative numbers and minus signs don't appear in the results string?

FORMULA in cell A1 =ROUND(E15,0)+ROUND(E16,3)+SUM(E17,6)+0+ROUND(SUM(E21,10)+B29,-1)+ROUND(E24-20,-2)+3

Thanks again - grateful for the help.

 

[Peter_SSs]

decimals to round to may be a negative number

Speaking about negative numbers, what do you want extracted for this red number in your formula, 20 or -20? The current formulas extract 20 but I presume you would want -20?.
=ROUND(E15,0)+ROUND(E16,3)+SUM(E17,6)+0+ROUND(SUM(E21,10)+B29,-1)+ROUND(E24-20,-2)+3
It is a similar question re decimal numbers. The current formula would extract 2, 3, and 4 from this formula but I assume it should be 2 and 3.4?
=2+3.4

Also note that both the previous formulas failed if the rounding was to 10 or more decimal places.

So a revised formula that extracts the blue values from this formula
=ROUND(E15,0)+ROUND(E16,13)+SUM(E17,6)+2.34+ROUND(SUM(E21,10)+B29,-1)+ROUND(E24-20,-2)+3+SUM($2:2)

There may be more/other circumstances to include/exclude numbers from the results that I have not thought of yet.

25 03 07.xlsm
	A
1	21.34
5	6, 2.34, 10, -20, 3
Extract Nums (2)

Cell Formulas
Range	Formula
A1	A1	=ROUND(E15,0)+ROUND(E16,13)+SUM(E17,6)+2.34+ROUND(SUM(E21,10)+B29,-1)+ROUND(E$24-20,-2)+3+SUM($2:2)
A5	A5	=TEXTJOIN(", ",,REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(FORMULATEXT(A1),"ROUND\(([A-Z]+\([^()]+\)[^()]+|[^()]+[^()]+),\-?\d+\)","\1"),"[A-Z$\(:]\d+","X"),"([\-]?\d+\.*\d*)",1))

 

[ChristineJ]

This works well - the numbers (positive or negative) indicating the number of decimal places for the ROUND function do not and should not appear in the result,

Thanks for thinking about additional scenarios. I would want the "2.34" to appear exactly as "2.34" in the A5 result (rather than 2, 34).

All numbers in the A5 result can be absolute values without minus signs: "20" rather than "-20".

The amounts in the final SUM function should appear in the A5 result: "2, 2"

So the result from =ROUND(E15,0)+ROUND(E16,13)+SUM(E17,6)+2.34+ROUND(SUM(E21,10)+B29,-1)+ROUND(E$24-20,-2)+3+SUM($2:2) in A5 should be

6, 2.34, 10, 20, 3, 2, 2

Thanks!

 

[Peter_SSs]

The amounts in the final SUM function should appear in the A5 result: "2, 2"

What is the logic to that? Those are parts of references to a row on the worksheet, just as the 15 in E15 or 16 in E16 etc. are, and you wanted those ones excluded.

I would want the "2.34" to appear exactly as "2.34" in the A5 result (rather than 2, 34).

All numbers in the A5 result can be absolute values without minus signs: "20" rather than "-20".

Apart from the "row 2" issue above, this slight modification to my previous formula should be pretty close then I think.

25 03 07.xlsm
	A
1	21.34
5	6, 2.34, 10, 20, 3
Extract Nums (2)

Cell Formulas
Range	Formula
A1	A1	=ROUND(E15,0)+ROUND(E16,13)+SUM(E17,6)+2.34+ROUND(SUM(E21,10)+B29,-1)+ROUND(E$24-20,-2)+3+SUM($2:2)
A5	A5	=TEXTJOIN(", ",,REGEXEXTRACT(REGEXREPLACE(REGEXREPLACE(FORMULATEXT(A1),"ROUND\(([A-Z]+\([^()]+\)[^()]+|[^()]+[^()]+),\-?\d+\)","\1"),"[A-Z$\(:]\d+","X"),"(\d+\.*\d*)",1))

 

[ChristineJ]

Your modification for A5 works removes the minus sign on -20, which works better for me.

As for the "2 issue" in +SUM($2:2), you're right on that one. I didn't know that would add all amounts in row 2! Learned something new. Since these "2s" act as cell references, I do not want them returned in A5.

6, 2.34, 10, 20, 3 is the desired result.

Now if I change the +SUM($2:2) in the formula to +SUM(2,5), both are numbers that are added. But the result is 6, 2.34, 10, 20, 3, 5 rather than 6, 2.34, 10, 20, 3, 2, 5 - is there a way to capture the 2 in the SUM as well?

Thanks so much - very helpful! C