# 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

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.

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.
`=(SUMPRODUCT(IFERROR(VALUE(REGEX(\$A14;"\d+";;ROW(INDIRECT("1:"&LEN(\$A14)))));0))+VALUE(REGEX(\$A14;"\d+";;1)))/2`
`=(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.

Change `0.5` by `0,5`