Extract Numbers From Text String And Sum Them

[simona123]

[TABLE="width: 134"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Dear friends, I need help.I have this:
A1: 6-2, 6-2[/TD]
[/TR]
[TR]
[TD]A2: 8-1, 4-3[/TD]
[/TR]
[TR]
[TD]A3: 6-2, 6-3,5-3
[/TD]
[/TR]
[TR]
[TD]And I want to sum all the numbers before the "-"
Example: In B1 i want to have 12 (6+6) from A1.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]



[TR]
[TD][TABLE="width: 134"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]

</tbody>[/TABLE]

 

[Haluk]

Before someone creates a shorter formula, try the following;

In cell B1 enter the formula:

=SUMPRODUCT(1*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"-";"");",";"");" ";"");ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"-";"");",";"");" ";""))));1))

You may need to change semicolons(";") to commas (",") depending on your Excel settings.

 

[Rick Rothstein]

=SUMPRODUCT(1*MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"-";"");",";"");" ";"");ROW(INDIRECT("1:"&LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"-";"");",";"");" ";""))));1))

You may need to change semicolons(";") to commas (",") depending on your Excel settings.

Your formula appears to be adding up all of the digits... the OP asked to add up the number in front of each dash.

 

[Rick Rothstein]

I am pretty sure there is a more compact formula to be had, but until someone posts it, give this one a try...

=IFERROR(SUMPRODUCT(0+LEFT(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))*100,100)),FIND("-",TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",100)),ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))*100,100)))-1)),"")


If you are looking for a VBA solution instead, then here is a UDF (user defined function) that you can try...

Function SumBeforeMinus(S As String) As Variant
  Dim V As Variant
  For Each V In Split(S, ",")
    SumBeforeMinus = SumBeforeMinus + Val(V)
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SumBeforeMinus just like it was a built-in Excel function. For example,

=SumBeforeMinus(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

If there is some smallish limit on how many sets of scores in a cell (I've used a limit of 6), you could also try this.

Book1
	A	B
1	6-2, 6-2	12
2	8-1, 4-3	12
3	6-2, 6-3,5-3	17
4	2-4, 15-1,12-13, 1-0	30
Sum Before Dash

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(IFERROR(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),",","-"),"-",REPT(" ",100)),{1,3,5,7,9,11}*100-99,99),0))

 

[simona123]

Thank you for helping me! It works.

 

[Peter_SSs]

Thank you for helping me! It works.

Glad you have something suitable. Not knowing which method suits you best though, if you are pursuing the UDF path, here is a one-liner for you to consider.

Function SumPreDash(s As String) As Double
  SumPreDash = Evaluate(Replace(Replace(s, ",", ")+"), "-", "-N(N1") & ")")
End Function

Book1
	A	B
1	6-2, 6-2	12
2	8-1, 4-3	12
3	6-2, 6-3,5-3	17
4	2-4, 15-1,12-13, 1-8	30
5	18-19	18
Sum Before Dash

Cell Formulas
Range	Formula
B1	=SumPreDash(A1)

 

[pgc01]

Hi

Another option for a udf:

Function AddLeft(s As String) As Double
    AddLeft = Evaluate(Replace(Replace(s, ",", "+"), "-", "-0*"))
End Function

 

[Peter_SSs]

Another option for a udf:

Function AddLeft(s As String) As Double
    AddLeft = Evaluate(Replace(Replace(s, ",", "+"), "-", "[COLOR="#FF0000"][B]-0*[/B][/COLOR]"))
End Function

Doh, why couldn't I think of that simple solution instead of the clumsy thing I came up with?