# Can't get the value of the cell substring

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

edit retag close merge delete

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.

( 2020-03-30 14:08:33 +0100 )edit

Sort by » oldest newest most voted

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

more

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

( 2020-03-30 17:20:43 +0100 )edit
1

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

( 2020-03-31 02:57:58 +0100 )edit

\\// 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

( 2020-03-31 13:48:43 +0100 )edit

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.

more

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 ",")?

( 2020-03-30 16:28:44 +0100 )edit

Change 0.5 by 0,5

( 2020-03-30 16:39:57 +0100 )edit