Ask Your Question

Can't get the value of the cell substring

asked 2020-03-30 11:50:01 +0100

piotrm gravatar image

updated 2020-08-30 11:59:57 +0100

Alex Kemp gravatar image

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 flag offensive 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.

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

2 Answers

Sort by » oldest newest most voted

answered 2020-03-30 15:01:33 +0100

Lupp gravatar image

updated 2020-03-30 15:02:27 +0100

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

edit flag offensive delete link more


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

piotrm gravatar imagepiotrm ( 2020-03-30 17:20:43 +0100 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2020-03-31 02:57:58 +0100 )edit

\\// Great! Greetings from Vulcan (Mr. Spock is wearing cufflinks today.)

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

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

answered 2020-03-30 14:17:37 +0100

Opaque gravatar image

updated 2020-03-30 16:52:31 +0100


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

edit flag offensive delete link more


In this I have a little problem. I work on LibreOffice 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 ",")?

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

Change 0.5 by 0,5

Opaque gravatar imageOpaque ( 2020-03-30 16:39:57 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-30 11:50:01 +0100

Seen: 170 times

Last updated: Mar 30 '20