Can't get the value of the cell substring

piotrm · March 30, 2020, 9:50am

Hello,
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
=LEFT($A6;SEARCHB("+";$A6;1)-1)+0,5*VALUE(MID($A6;SEARCHB("+";$A6;1)+1;LEN(A6)))
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 :-)?
Thanks
Piotr

anon73440385 · March 30, 2020, 12:08pm

You get Err:502, since MID($A6;SEARCHB("+";$A6;1)+1;LEN(A6)) equals 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.

Lupp · March 30, 2020, 1:01pm

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

piotrm · March 30, 2020, 3:20pm

WOW, it works, however I dont grasp how :-).Many thanks :-).

mariosv · March 31, 2020, 12:57am

Seems offset can be eliminated reducing a bit the formula length.
=(SUMPRODUCT(IFERROR(VALUE(REGEX($A14;"\d+";;ROW(INDIRECT("1:"&LEN($A14)))));0))+VALUE(REGEX($A14;"\d+";;1)))/2
perhaps more adjusted
=(SUMPRODUCT(VALUE(REGEX($A25;"\d+";;ROW(INDIRECT("1:"&1+LEN(REGEX($A25;"[^+]";"";"g")))))))+VALUE(REGEX($A14;"\d+";;1)))/2

Lupp · March 31, 2020, 11:48am

\// Great! Greetings from Vulcan (Mr. Spock is wearing cufflinks today.)
*III

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.):
340+(43+23+32+2)/2 or (340+340+43+23+32+2)/2

anon73440385 · March 30, 2020, 12:17pm

Hello,

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));"+")))

Update [for , 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: 6.4.2.2; 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.

piotrm · March 30, 2020, 2:28pm

In this I have a little problem. I work on LibreOffice 6.4.2.2. 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 “,”)?

anon73440385 · March 30, 2020, 2:39pm

Change 0.5 by 0,5