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

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

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

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

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

Change 0.5 by 0,5