I have in a cell two numbers "=5 4" as a text. This is a result of another operation.

I took a part of another formula and concatenated it with "equal" symbol:

= "="   &    RIGHT(FORMULATEXT(V8);LEN(FORMULATEXT(V8))-SEARCH(" "; FORMULATEXT(V8)))

I want to get the result of 5 4 in a cell- which means "9" ;) I DO NOT WANT TO USE VBA code.

CodePudding user response：

If you are always adding 2 numbers, then you can use this:

=VALUE(VALUE(MID(A1;2;SEARCH(" ";A1)-2)) VALUE(MID(A1;SEARCH(" ";A1) 1;9999)))

My column A is formatted as text, and all values follow same pattern: =Value1 Value2

So the formula extracts Value1 and Value2 as text with MID functions, based on the position of symbol. Then Excel convert both values into numeric with VALUE function. Notice there are 3 values, the third one is to make sure the cell stays at standar format (in some versions of Excel, like mine 2007, when involving text formatted cells into formulas, the formulated cell autoformat itself to text format, making the formula to work just once).

As you can see in the image, it works perfectly but this is just for pattern =Value1 Value2. For different patters you'll need different formulas.

If you want to make a formula to work with all patterns, then indeed you need VBA.

CodePudding user response：

my initial problem was to extract all numbers except the first one from an equation and sum in another cell: A1: "=6 5 4". A2: "9". Maybe it can be solved without VBA?

You could try (assuming only addition):

Formula in B1:

=SUM(FILTERXML("<t><s>"&SUBSTITUTE(MID(A1,2,LEN(A1))," ","</s><s>")&"</s></t>","//s[position()>1]"))