Hi:
If, for example, cell A1 contains a formula AVERAGE(1;2;3), how can I get values 1 in cell B1, 2 in cell C1 and 3 in cell D1.
Is it possible at all.
Thank you and regards.
Bojan
Does it contain a formula =AVERAGE(1;2;3), or a text AVERAGE(1;2;3)?
It only makes a difference because for the former case, you would start with =FORMULA(A1), while for the latter case, you would use A1 directly.
Then you would parse the string, and obtain everything inside parentheses, and split by ;, and get the Nth occurrence … possibly all using REGEX.
But why? It will all become useless as soon as you move the sheet to a locale where argument separators are commas; or when you use AVERAGE(A2:A4); or when you have more complex formulas (either enclosing the AVERAGE, or enclosed inside it)…
1 Like
Thank you.
Said formula contains numbers not text. Nevertheless, it looks complicated proceeding to me.
Usually one enters values into cells and lets the formula work with cell references instead… i.e.
B1: 1
C1: 2
D1: 3
A1: =AVERAGE(B1:D1)
If you started the wrong way there isn’t much else than manually converting except what Mike said, do it partially automated by writing sophisticated but fragile formulas or macros to split the numbers off into cells.
3 Likes
The REGEX() result is text, to further calculate with it should be converted to numeric, like
=NUMBERVALUE(REGEX(FORMULA($A1);"\d+"))
If the numbers contain decimal separators, the regex patterns need to be adapted and the correct separator be passed to NUMBERVALUE() as second parameter (which may differ in different locales).
To have it working in all locales, the final result should be converted by copying the cells with the extracted formula results to clipboard and using Paste Special (Shift+Ctrl+V) paste only values, not formulas.
=Mid(3/2;2;1)
1 Like