Help..total multiple entries in a single cell...

[bjack56]

I have the following information in a CELL, let's call it A1. These are
just project dates with working hours in ( ).

12/1(5.5),12/5(10.5),12/10(4)

In cell A2 I would like a formula that totals only the numbers inside
the ( ) in a single cell. The above example would = 20.0

 

[Dave Peterson]

I think you'll need a User defined function.

Option Explicit
Function sumInParens(rng As Range) As Variant

Dim myStrOut As String
Dim myStrIn As String
Dim tempVal As Variant
Dim iCtr As Long
Dim keepNext As Boolean

Set rng = rng(1) 'just the first cell

myStrIn = rng.Value
myStrOut = ""
For iCtr = 1 To Len(myStrIn)
If Mid(myStrIn, iCtr, 1) = "(" Then
keepNext = True
myStrOut = myStrOut & "+"
ElseIf Mid(myStrIn, iCtr, 1) = ")" Then
keepNext = False
Else
If keepNext Then
myStrOut = myStrOut & Mid(myStrIn, iCtr, 1)
End If
End If
Next iCtr

tempVal = Application.Evaluate(myStrOut)
If IsError(tempVal) Then
sumInParens = CVErr(xlErrNA)
Else
sumInParens = tempVal
End If

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type
=suminparens(a1)
in a cell (but point at the cell with the string).

 

[Guest]

Here's one really, really long formula you can use:

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)+MID(A1,FIND("(",A1,FIND("(",A1)+1)+1,FIND(")",A1,FIND(")",A1)+1)-FIND("(",A1,FIND("(",A1)+1)-1)+MID(A1,FIND("(",A1,FIND("(",A1,FIND("(",A1)+1)+1)+1,FIND(")",A1,FIND(")",A1,FIND(")",A1)+1)+1)-FIND("(",A1,FIND("(",A1,FIND("(",A1)+1)+1)-1)

However, this is only good for three sets of numbers. If you go to four it gets much worse.

If you want to keep it more manageable you could break down the above into multiple components:

B1osition of first ( =FIND("(",A1)
C1osition of first ) =FIND(")",A1)

D1osition of second ( =FIND("(",A1,B1+1)
E1osition of second ) =FIND(")",A1,C1+1)

F1osition of second ( =FIND("(",A1,D1+1)
G1osition of second ) =FIND(")",A1,E1+1)

H1:First Number =MID(A1,B1+1,C1-B1-1)
I1:Second Number =MID(A1,D1+1,E1-D1-1)
J1:Third Number =MID(A1,F1+1,G1-F1-1)

Total =H1+I1+J1 (note: you can't use SUM for this step)

Good Luck,
Mark Graesser
(e-mail address removed)


----- bjack56 wrote: -----

I have the following information in a CELL, let's call it A1. These are
just project dates with working hours in ( ).

12/1(5.5),12/5(10.5),12/10(4)

In cell A2 I would like a formula that totals only the numbers inside
the ( ) in a single cell. The above example would = 20.0

 

[Harlan Grove]

I think you'll need a User defined function.

...

Not strictly necessary. If the OP's data format is assured, then it could be
done with a defiend name and an array formula. The defined name would be one of
my favorites.

Seq referring to =ROW(INDIRECT("1:1024"))

If the cell containing the formatted data were B2, the array formula would be

=SUM(--MID(B2,SMALL(IF(MID(B2,Seq,1)="(",Seq),
ROW(INDIRECT("1:"&SUMPRODUCT(--(MID(B2,Seq,1)="(")))))+1,
SMALL(IF(MID(B2,Seq,1)=")",Seq),
ROW(INDIRECT("1:"&SUMPRODUCT(--(MID(B2,Seq,1)=")")))))
-SMALL(IF(MID(B2,Seq,1)="(",Seq),
ROW(INDIRECT("1:"&SUMPRODUCT(--(MID(B2,Seq,1)="(")))))-1))

 

[Harlan Grove]

...
...

If you want to keep it more manageable you could break down the above into
multiple components: ...
B1osition of first ( =FIND("(",A1)
C1osition of first ) =FIND(")",A1)

Better to use =FIND("(",A1)+1 in cell B1 and =FIND(")",A1,B1) in cell C1 to
ensure that you're looking for the first right parenthesis following the first
left parenthesis.

D1osition of second ( =FIND("(",A1,B1+1)
E1osition of second ) =FIND(")",A1,C1+1)

Better to use =FIND("(",A1,C1)+1 in cell D1 and =FIND(")",A1,D1) in cell E1.

F1osition of second ( =FIND("(",A1,D1+1)
G1osition of second ) =FIND(")",A1,E1+1)

Better to use =FIND("(",A1,E1)+1 in cell F1 and =FIND(")",A1,F1) in cell G1.

H1:First Number =MID(A1,B1+1,C1-B1-1)
I1:Second Number =MID(A1,D1+1,E1-D1-1)
J1:Third Number =MID(A1,F1+1,G1-F1-1)

Total =H1+I1+J1 (note: you can't use SUM for this step)

If you use the alternative formulas I've given previously, these formulas would
become

H1: =MID(A1,B1,C1-B1)
I1: =MID(A1,D1,E1-D1)
J1: =MID(A1,F1,G1-F1)

Better still to make these

H1: =--MID(A1,B1,C1-B1)
I1: =--MID(A1,D1,E1-D1)
J1: =--MID(A1,F1,G1-F1)

Then you could use =SUM(H1:J1) to sum them.

 

[Dave Peterson]

I should have said:

I think _I_ would need a UDF!

...
..

Not strictly necessary. If the OP's data format is assured, then it could be
done with a defiend name and an array formula. The defined name would be one of
my favorites.

<<snipped>>