Can't get the value of the cell substring

edit

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
C:\fakepath\ask235959funnyExpressionEvaluated_1.ods.

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.