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

 

[Snakehips]

mrxlsx,

Maybe this code for a user defined function will help.

Copy the code to a code Module.

Function SUMTEXT(Txt As Range, LWH As Range)
SUMTEXT = 1
nums1 = Split(Txt, " ")
u1 = UBound(nums1)
nums2 = Split(nums1(u1), "x")
For c = 0 To UBound(nums2)
SUMTEXT = nums2(c) * SUMTEXT
Next c
For Each cell In LWH
If IsNumeric(cell) Then
If cell > 0 Then SUMTEXT = SUMTEXT * cell
End If
Next cell
If SUMTEXT = 1 Then SUMTEXT = ""
End Function

Then use as per example......

Excel 2007
	A	B	C	D	E
1	Work Description	Length	Width	Height	Total
2	Parapet wall 2x4x8	12	2	4	6144
3	Kerbway 4x2	4	2	3	192
4	Flooring 2x4x4x2	5	8	12	30720
Sheet3

Cell Formulas
Range	Formula
E2	=sumtext(A2,B2:D2)

Hope that helps.

 

[Michael M]

Kudos Tony, great snippet.....ray:

 

[pgc01]

Hi

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

If I understood correctly, what you really want is:

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

If you prefer a formula solution, try in E1:

=PRODUCT(IFERROR(0+TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ","x"),"x",REPT(" ",100)),{100,200,300,400}),100)),1),B1:D1)

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Copy down


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >E</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >F</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Parapet wall 2x4x8</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">12</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">6144</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Kerbway 4x2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">192</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Flooring 2x4x4x2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">8</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">12</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">30720</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Ceiling 3.5x4.5x14</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">4</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">5</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">6</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">26460</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=7 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[mrxlsx]

Hi Snakehips and pgc01,

Thanks a lot for your kind help. Both are working just fine for now. I will test use both the solutions for a week and select the apt solution for my situation later. But I need those numbers in the description column to be extracted to another column say in G1( i.e. just 2x4x8). How to do that???

Thank you very much for the great help.

 

[Rick Rothstein]

 Function SUMTEXT(Txt As Range, LWH As Range)
SUMTEXT = 1
nums1 = Split(Txt, " ")
u1 = UBound(nums1)
nums2 = Split(nums1(u1), "x")
For c = 0 To UBound(nums2)
SUMTEXT = nums2(c) * SUMTEXT
Next c
For Each cell In LWH
If IsNumeric(cell) Then
If cell > 0 Then SUMTEXT = SUMTEXT * cell
End If
Next cell
If SUMTEXT = 1 Then SUMTEXT = ""
End Function

I thought you (and others reading this thread) might be interested to see how your UDF can be written with just one line of code (I used a line continuation character to split the display of code line to make it fit in this forum's comment box area better)...

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

 

[pgc01]

Hi Rick

Another option for the second part of the statement:

Function SumText(Txt As String, LWH As Range) As Double
  SumText = Evaluate(Replace(Mid(Txt, InStrRev(Txt, " ")), "x", "*")) * Application.Product(LWH)
End Function

 

[Rick Rothstein]

Hi Rick

Another option for the second part of the statement:

Function SumText(Txt As String, LWH As Range) As Double
  SumText = Evaluate(Replace(Mid(Txt, InStrRev(Txt, " ")), "x", "*")) * Application.Product(LWH)
End Function

Yeah, that's a good one (it shortens the formula up nicely)... for some reason, I keep forgetting that the PRODUCT function exists. There is one difference between our formulas though... if the table is missing one of its length or width or height values, my fomula issues a #VALUE! error to let the user know something is wrong whereas your formula simply multiplies the values that are present which, in turn, may cause the user to not notice a value is missing in the table.

 

[pgc01]

You're right, Product() ignores non numeric values (except error values). Good point.

 

[Snakehips]

Rick, PG,

My solution is indeed a 'shaggy dog story' compared to your slick 'one liners' so I will leave you two to compete for the Mr Excel 'Bob Hope' award 2014

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?

I would suggest that mrxlsx tests whichever of the solutions he might use to see if he happy with the outcomes when the data is in anyway incomplete.

mrxlsx,

In response to your request to have the 'number element' in column G then......

If you are happy that there will always be a 'number element' at the tail of the string in A then maybe...

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

If you need to test for presence of the number element then maybe.....

=IF(ISNUMBER(SEARCH("x",TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)))),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)),"")