• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate multiple numbers from text

T

T39m

New Member
Hello. Hope you can help me. Here it goes: I need to create multiple lists with different measurements in different areas. In the first cell is the text that begins with the area (E1) and continues with the measurements (product or sum). It can be best seen in the following table:
2cet3b7.png

Columns A,B,C, D & E are merged and column F is (in this case) the product of the values from the first cell. I tried using this formula: =SUMPRODUCT(- -ISNUMBER(MID(A1,ROW($A$1:$A$200),1)+0)) presented on thy website, but found it to be valid for one number, not multiple ones.
To be more clear, I only want to multiply, add or subtract values such as "9,43", "5,60", "3,16" from the text in the merged cells. The numbers represent measurements and decimals may vary from 1 to 3.

Awaiting your blessed help :)
 
T39m said:
Hello. Hope you can help me. Here it goes: I need to create multiple lists with different measurements in different areas. In the first cell is the text that begins with the area (E1) and continues with the measurements (product or sum). It can be best seen in the following table:
2cet3b7.png

Columns A,B,C, D & E are merged and column F is (in this case) the product of the values from the first cell. I tried using this formula: =SUMPRODUCT(- -ISNUMBER(MID(A1,ROW($A$1:$A$200),1)+0)) presented on thy website, but found it to be valid for one number, not multiple ones.
To be more clear, I only want to multiply, add or subtract values such as "9,43", "5,60", "3,16" from the text in the merged cells. The numbers represent measurements and decimals may vary from 1 to 3.

Awaiting your blessed help :)
Click to expand...
Hi,

If what you've posted is representative of your data then this will pull out the first number

=TRIM(MID(SUBSTITUTE(" " & A14&REPT(" ",6)," ",REPT(" ",255)),3*255,255))

and this the second

=TRIM(MID(SUBSTITUTE(" " & A14&REPT(" ",6)," ",REPT(" ",255)),6*255,255))

Both the above are for A14.

Both the numbers pulled out are text values but the action of multiplying, adding or subtracting will coerce the text to a number.

Alternatively you can prefix each formula with -- and that will coerce the text to a number.
 
Can you upload a sample Excel as it will give clearer picture?

You can of course try following blind shot.
1. Goto Formulas >> Name Manager >> New Name
2. Assign name: Evaluate
and insert following formula in refers to field:
=EVALUATE(SUBSTITUTE(TRIM(MID(SUBSTITUTE(Sheet1!A14," ",REPT(" ",99)),99*2,99)),",",".")&SUBSTITUTE(TRIM(MID(SUBSTITUTE(Sheet1!A14," ",REPT(" ",99)),99*4,99)),",",".")&SUBSTITUTE(TRIM(MID(SUBSTITUTE(Sheet1!A14," ",REPT(" ",99)),99*5,99)),",","."))

And then in cell type formula.
=Evaluate
Copy down.

Please note the Evaluate function is Excel 4.0 function which can be used only through named ranges.

I am attaching Excel file for your reference.
 

Attachments

Hi,

Considering your text has * in it try below formula:

=MID(F15,FIND(" - ",F15)+3,FIND(" m",F15)-FIND("- ",F15)-2)*MID(F15,FIND(" * ",F15)+3,LEN(F15)-FIND(" * ",F15)-4)

Text in F15.

Regards,
 
All of the answers are valid and I thank you very much for the quick responses. But I can't seem to make them work for the more complicated calculations I have.
Here is one of the lists. Sorry for not posting this in the first place.
 

Attachments

What I had suggested could work for above cases as well. See attached.

But keep in mind that the formula thus used are math operations and no more. e.g. If you were to use different units then the formula may not be giving correct answer.
 

Attachments

You must log in or register to reply here.
Back
Top