Ask Your Question
0

How to sum all numbers, separeted by dots, from a cell?

asked 2019-10-27 20:37:03 +0100

cipj gravatar image

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.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2019-10-27 23:14:08 +0100

erAck gravatar image

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...

edit flag offensive delete link more

Comments

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..

erAck gravatar imageerAck ( 2019-10-27 23:48:01 +0100 )edit

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.

Lupp gravatar imageLupp ( 2019-10-28 02:18:29 +0100 )edit
0

answered 2019-10-30 07:48:33 +0100

cipj gravatar image

Thank you for your answers.

edit flag offensive delete link more
0

answered 2019-10-28 02:07:57 +0100

Lupp gravatar image

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: C:\fakepath\ask214807textSplit_1.ods

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-27 20:37:03 +0100

Seen: 79 times

Last updated: Oct 30