I have cell eg. A6 with text strings like “340+43+23+32+2” and I need to compute in other column the sum of the first and the half of the sum of all other numbers in a cell.
If I enter
I have an error 502.
Can’t see any function which is capable of do a calculation on a portion of a text string in a cell, even if it is a fully meaningful formula content, like “43+23+32+2”.
Does anyone have a solution :-)?
43+23+32+2 which cannot be type converted to a value (it is still a string an not a formula). In addition:
LEFT($A6;SEARCHB("+";$A6;1)-1) keeps text and is not a number.
If you are working with LibreOffice Version 6.2 or higher you can use the new REGEX() fiunction as a provisional surrogate for the long missing TEXTSPLIT() function in such a case:
=(SUMPRODUCT(VALUE(REGEX(A6;"\d+";;ROW(OFFSET(INDIRECT("a1");0;0;1+LEN(A6)-LEN(SUBSTITUTE(A6;"+";""))))))) + VALUE(REGEX(A6;"\d+";;1)))/2
That’s really simple, isn’t it?
To tell it clearly: This (the formula) is not a joke!
You find it demonstrated (and commented) in the attached
WOW, it works, however I dont grasp how :-).Many thanks :-).
Seems offset can be eliminated reducing a bit the formula length.
perhaps more adjusted
\// Great! Greetings from Vulcan (Mr. Spock is wearing cufflinks today.)
I like the second version.
However: It is a dangerous (misleading) thing to calculate results definitely NOT belonging to a shown expression. To show a sum, and next to it a value NOT being the result: Why?
The calculated expression for the first example from the OQ could be (e.g.):
one solution might be (all text needs to be converted to numbers):
Define the following user function (macro - you may define a different name )
Function TOARRAY(sInput as string, sSepar as string ) as Variant REM sInput: String to be split REM sSepar: Separator separating string elements from each other REM No input/error handling at all (just for demonstration purposes) TOARRAY = Split (sInput, sSepar) End Function
Use the following formula
=VALUE(LEFT($A6;SEARCHB("+";$A6;1)-1)) + 0.5*SUMPRODUCT(VALUE(TOARRAY(MID($A6;SEARCHB("+";$A6;1)+1;LEN(A6));"+")))
, delimited locales]
=VALUE(LEFT($A6;SEARCHB("+";$A6;1)-1)) + 0,5*SUMPRODUCT(VALUE(TOARRAY(MID($A6;SEARCHB("+";$A6;1)+1;LEN(A6));"+")))
Tested using LibreOffice:
Version: 188.8.131.52; Build ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3 CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5; Locale: en-US (en_US.UTF-8); UI-Language: en-US; Calc: threaded
Note I’m neither aware of any predefined function in Calc which splits a textual string into an array of numbers, nor of any way to convert a text into a formula like other tools allow (e.g
eval in bash shell).
Hope that helps.
If the answer helped to solve your problem, please click the check mark () next to the answer.
In this I have a little problem. I work on LibreOffice 184.108.40.206. on Kubuntu 18.4.Yet I managed to put the proposed function into my Standard and Module1. But it doesnt appear ready to use in the document.
Can it be related to my polish local function names or settings (";" in functions instead of “,”)?