How to SUM a Text.

[sajjjid]

A1=((2000-700)x1.5) this is a text.
@ B1 i need to sum this text as =sum((2000-700)*1.5)
Thanks.

 

[NARAYANK991]

Hi ,

Isn't this the same question you have asked here ?

http://chandoo.org/forum/threads/need-formula.34226/

Narayan

 

[sajjjid]

Hi ,

Isn't this the same question you have asked here ?

http://chandoo.org/forum/threads/need-formula.34226/

Narayan

Yes. I am looking for a formula instead of a macro. Thanks.

 

[NARAYANK991]

Hi ,

The solution provided in answer to the question is not a macro ; it is a formula , but because it makes use of unsupported Excel 4 Macro functions , when saving the file containing these functions , the file has to be saved as a macro file.

I am not sure if the more recent versions of Excel have functions which can be used to solve your problem , but versions till Excel 2010 do not have any native Excel function to do what you want.

Either you accept the solution provided earlier , or you ensure that your data is not in text format.

Narayan

 

[sajjjid]

I hope there is some solution.

 

[chirayu]

attached - the only other possible solution I could find.

@NARAYANK991 fyi

 

[NARAYANK991]

attached - the only other possible solution I could find.

@NARAYANK991 fyi

Hi ,

Isn't this the same answer that was given in the other question ?

Narayan

 

[chirayu]

@NARAYANK991 DOH! lol my mistake - I never read the original post I just read this one & poster said no macro solution so I assumed it was a vba code in the other post. Didn't realize its the same thing. On the plus side - Substitute! lol

 

[Hui]

Give cell A1 a Name eg: Formula
Goto the Name Manager
Add a name
Name: Function
Refers to: =SUM(EVALUATE(SUBSTITUTE(Formula,"x","*")))

Exit the name manager

In cell B1 =Function

or see the attached file:

Enjoy

 

[bosco_yip]

Another example in Text calculation with description as in :



1] In A2:A4, enter measurement breakdown data

2] Goto Define Name >>

Name: TextCalcu

Refers to: =EVALUATE(SUBSTITUTE(SUBSTITUTE(INDIRECT("rc[-1]",0),"[","*ISTEXT(""["),"]","]"")"))

3] In B2, formula copy down :

=TextCalcu

4] See attached file

Regards
Bosco

 

[sajjjid]

Give cell A1 a Name eg: Formula
Goto the Name Manager
Add a name
Name: Function
Refers to: =SUM(EVALUATE(SUBSTITUTE(Formula,"x","*")))

Exit the name manager

In cell B1 =Function

or see the attached file:

Enjoy

tried. not succeed. In B2-B3-B4 it gives same answer.

 

[NARAYANK991]

tried. not succeed. In B2-B3-B4 it gives same answer.

Hi ,

The reason is that when you name a range , Excel makes it an absolute reference.

Go to the Name Manager , and edit the named range Formula , so that instead of referring to $A$1 , it refers to $A1.

Now , when you use this in cell B1 , the reference will be to the text in cell A1 , when you use the same in cell B2 , the reference will be to the text in cell A2 , and so on.

Narayan

 

[sajjjid]

Another example in Text calculation with description as in :

View attachment 42028

1] In A2:A4, enter measurement breakdown data

2] Goto Define Name >>

Name: TextCalcu

Refers to: =EVALUATE(SUBSTITUTE(SUBSTITUTE(INDIRECT("rc[-1]",0),"[","*ISTEXT(""["),"]","]"")"))

3] In B2, formula copy down :

=TextCalcu

4] See attached file

Regards
Bosco

Thanks. This is also useful for calculating LxBxH.

 

[sajjjid]

Hi ,

The reason is that when you name a range , Excel makes it an absolute reference.

Go to the Name Manager , and edit the named range Formula , so that instead of referring to $A$1 , it refers to $A1.

Now , when you use this in cell B1 , the reference will be to the text in cell A1 , when you use the same in cell B2 , the reference will be to the text in cell A2 , and so on.

Narayan

Thanks. Now it works.