Sum numbers from a text string

[mrxlsx]

Hi all, Good afternoon everybody,

I am struggling with a problem which involves a column with both the text and nos in that. I want to calculate the following formula.

Total = the Sumproduct of the nos in the description*Length*Width*Height.

In the description column,
1) The nos will be varying i.e x*x or x*x*x or X*x*x*x*.
2) After the nos there will not be any data.

Example table:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Work Description
[/TD]
[TD="align: center"]Length[/TD]
[TD="align: center"]Width[/TD]
[TD="align: center"]Height[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD]Parapet wall 2x4x8[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6144[/TD]
[/TR]
[TR]
[TD]Kerbway 4x2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]192[/TD]
[/TR]
[TR]
[TD]Flooring 2x4x4x2[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]30720[/TD]
[/TR]
</tbody>[/TABLE]







Is there a way to achieve this, what is the formula for this ??

Please help me as I am struggling with this for a longtime.

mrxlsx

 

[Rick Rothstein]

Rick, for me, your UDF would appear to return an erroneous value rather than an error if either the Length or Width values are missing?

Darned if you are not right... I completely missed that. The reason why it issues an erroneous value is quite interesting... because of the way I concatentate things, a missing value in the first or second cell results in par of the number to be calculated being a number followed by two asterisks followed by another number... and in an Excel cell (effectively, how Evaluate calculates its results), a double asterisk is short for "raise 10 to the power of", so in a cell 2**5 becomes 200000 (the cell is calculated like this... 2*10^5)... hence, instead of raising an error, the Evaluate function simply computes the power of 10 when it sees the double astersk. The fix to my UDF appears to be simple (I just added a single space after the asterisk inside the Index function call), but I want to thank you for pointing out the problem to me in the first place...

Function SumText(Txt As String, LWH As Range) As Double
  SumText = Evaluate(Replace(Mid(Txt, InStrRev(Txt, " ")) & "*" & Join(Application. _
                     Index(LWH.Value, 1, 0), "* "), "x", "*", , , vbTextCompare))
End Function

 

[Snakehips]

Darned if you are not right... but I want to thank you for pointing out the problem to me in the first place...

As ever, thanks for the education.


BTW, MichaelM, thanks for the Kudos post #3. I basked in it for a short while until 'Bob Hope' pitched in!

 

[Rick Rothstein]

BTW, MichaelM, thanks for the Kudos post #3. I basked in it for a short while until 'Bob Hope' pitched in!

Okay, you have mentioned it again and I just do not understand the reference... what is this "Bob Hope" thing you keep mentioning (he was a famous comedian, so I do not see how his name applies to our discussion)

 

[Snakehips]

Okay, you have mentioned it again and I just do not understand the reference... what is this "Bob Hope" thing you keep mentioning (he was a famous comedian, so I do not see how his name applies to our discussion)

I have often seen you post 'one line' solutions. Was Bob Hope not regarded as the master of the one liners?

=IFERROR("Bob Hope", "Groucho Marx")

Does the analogous reference to my solution being a 'shaggy dog story', by comparison to your one liner, cross the Atlantic? A shaggy dog story being a long and protracted joke, often with a pathetically disappointing end.

 

[Rick Rothstein]

I have often seen you post 'one line' solutions. Was Bob Hope not regarded as the master of the one liners?

=IFERROR("Bob Hope", "Groucho Marx")

Does the analogous reference to my solution being a 'shaggy dog story', by comparison to your one liner, cross the Atlantic? A shaggy dog story being a long and protracted joke, often with a pathetically disappointing end.

Ah, Bob Hope = One liners... now I get it! As for the "shaggy dog story"... that is a completely new one for me, so I'm guessing it is not common or well-known on this side of the "pond".

 

[mrxlsx]

Hi Rick and Snakehips,

Both the solutions are working fine. Thanks for rolling up your sleeves.

mrxlsx