Convert text to equation and return the sum result in Google Sheets-CodePudding

Let's say I have text in the following format in Column A imported to another spreadsheet (impossible to add = manually because the data is imported automatically and change):

Is there any formula that can convert this text into an equation that gives the result of the sum in Column B?

For example:

=ARRAYFORMULA(FUNCTIONTOCONVERTTEXTTOEQUATION(A1:A))

Expected Result:

Note: The texts will always be a number after the sign and then another number.

CodePudding user response：

Try, assuming the imported data starts at A1

=arrayformula(sum(value(split(A1," "))))

or, in a single formula at the top of the column

=mmult(arrayformula(value(split(A1:A4," "))),sequence(2,1,1,0))

CodePudding user response：

Given your response to my clarifying question above, let's assume that your raw data is in A2:A. Place the following in the Row-2 cell (e.g., B2) of an otherwise empty column:

=ArrayFormula(IF(A2:A="",,MMULT(IFERROR(TRIM(SPLIT(A2:A," "))*1,0),SEQUENCE(COLUMNS(SPLIT(A2:A," ")),1,1,0))))

MMULT is a powerful yet underused function. I'll include a graphic that explains what it does better than words might:

SPLIT will form the elements of the first matrix, while SEQUENCE will simply create the second matrix consisting of a column of 1's the same length as the number of horizontal elements formed by the SPLIT (which, in your case, will apparently always be 2).