Hi,
I want to sum all the numbers in a cell (in LibreOffice Calc). The numbers are separated by dots. For example, cell A2 is composed of [10.10.10.10.5.10.8.10.2.10] (without square brackets). How could I gather and sum these numbers in another cell?
Thanks.
You can use a regular expression iterating over occurrences:
=SUM(VALUE(IFNA(REGEX(A2;"[0-9]+(?=.|$)";;ROW(A1:A23));0)))
entered as array formula, close with Shift+Ctrl+Enter instead of just Enter. The ROW(A1:A23) part here in array context creates an array of a 1;2;3;…;23 sequence where 23 is the maximum number of occurrences processed. The IFNA() catches the #N/A error for not present occurrences and feeds a 0 for those to SUM(). For your given example 10 elements of ROW(A1:A10) would be sufficient.
Regular expression processing can be quite expensive though if massively used, so for a large cell range it would be better to split cells into columns using the . dot as separator, under menu Data → Text to Columns…
2 Likes
For the given example the (?=.|$) lookahead isn’t even necessary and this would work as well:
=SUM(VALUE(IFNA(REGEX(A2;"[0-9]+";;ROW(A1:A23));0)))
Spares some extra cycles…
2 Likes
The ROW(A1:A23) may be insufficient for a different example and may be automatically adapted in an usuitable way if the formula is copy-pasted, and even if absolute address format is chosen problems may occur if rows are deleted.
The hardened version for tjhr ROW part would be
ROW(OFFSET(INDIRECT("a1");0;0;LEN(A2)-LEN(SUBSTITUTE(A2;".";""))+1;1)) and the complete formula
=SUMPRODUCT(VALUE(IFNA(REGEX(A2;"[0-9]+(?=.|$)";;ROW(OFFSET(INDIRECT("a1");0;0;LEN(A2)-LEN(SUBSTITUTE(A2;".";""))+1;1)));0)))
which is not quite simple.
1 Like
The REGEX() function is great. It should probably nonetheless not be charged with splitting lists.
LibreOffice has the TEXTJOIN() function now for some time. It is not quite plausible that a corresponding TEXTSPLIT() function is still missing. The BASIC coming with LibO always had its Split() function on the other hand.
I spended the time to make a demo containing a raw version of a TEXTSPLIT() function imlemented by user code in BASIC - and an even rawer completion also allowing for single column/row arrays as list parameters…
IMO TEXTJOIN() and TEXTSPLIT() implemented as standard functions should both also allow to exclude repetitions. The second parameter might be used for this purpose additionally. The functions included with the example don’t include this feature. A useful parameter passing a mimum length for the output sequence to lock in a sheet is included.
The example: ask214807textSplit_1.ods
Thank you for your answers.