LinkBack URL
About LinkBacks
bmsra79 
I have the following text populated in an Excel cell:
Task 1 (3 hrs); Task 2 (4 hrs); Task 3 (1 hr)
I need to calculate the sum of (3+4+1) = 8 hrs and populate this in another cell.
I have the following text populated in an Excel cell:
Task 1 (3 hrs); Task 2 (4 hrs); Task 3 (1 hr)
I need to calculate the sum of (3+4+1) = 8 hrs and populate this in another cell.
Based on your example:
=IF(ISNUMBER(SEARCH(" hr",A1)),SUM(IF(MID(A1,ROW(A$3:INDEX(A:A,LEN(A1)-3)),3)=" hr",--SUBSTITUTE(MID(A1,ROW(A$3:INDEX(A:A,LEN(A1)-3))-2,3),"(",""))),"")
confirmed with CTRL + SHIFT + ENTER
assumes no individual hour value ever exceeds 99 and that the string pattern is as consistent as outlined.
Last edited by DonkeyOte; 04-26-2010 at 03:29 AM. Reason: added $ around A3 in case being copied vertically
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
I have several cells populated in similar fashion: Example
Task 55 (8 hrs) --> Here it should extract 8
Task 22 (1 hr); Task 23 (5 hrs); Task 24 (2 hrs) ---> Here it should take (1+5+2=8) automatically
I need a way for this formula to automatically find the number of tasks in the cell and do the addition.
DonkeyOte: The code is not working???
DonkeyOte's code workes fine with me.
Did you confirm with CTRL + SHIFT + ENTER
Great. THanks. It works !!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks