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