I have a sheet with a resource count than can be blank or contain from 1- 5. For each resource count there is a corresponding work effort in the next column, separated by a comma (see table below). I am trying to extract the work effort totals into separate cells WE1 through WE5.
Objective:
Using formulas found in the SS community, I believe I have most of the formulas working. However, I am having issues with the WE3 and WE4.
Disclaimer - I am just beginning to learn about LEFT, RIGHT, MID, SUBSTITUTION.
Result:
WE1
=IF([Resource Count]@row = 1, [Work Effort]@row, (IF([Resource Count]@row > 1, (LEFT([Work Effort]@row, FIND(",", [Work Effort]@row) - 1)))))
WE2
=IF([Resource Count]@row >= 3, (MID([Work Effort]@row, FIND(",", [Work Effort]@row) + 1, FIND(",", [Work Effort]@row, FIND(",", [Work Effort]@row) + 2) - FIND(",", [Work Effort]@row) - 1)))
WE3
=IF([Resource Count]@row >= 3, (IF(SUBSTITUTE([Work Effort]@row, ",", "~", 3 - 1) = [Work Effort]@row, "", IF(SUBSTITUTE([Work Effort]@row, ",", "~", 3) = [Work Effort]@row, RIGHT([Work Effort]@row, LEN([Work Effort]@row) - FIND(",", SUBSTITUTE([Work Effort]@row, ",", "~", 3 - 1))), MID([Work Effort]@row, FIND("~", SUBSTITUTE([Work Effort]@row, ",", "~", 3 - 1)) + 1, FIND(",", [Work Effort]@row, FIND("~", SUBSTITUTE([Work Effort]@row, ",", "~", 3)) - 1) - FIND(",", [Work Effort]@row, FIND("~", SUBSTITUTE([Work Effort]@row, ",", "~", 3 - 1))) - 1)))))
WE4
=IF([Resource Count]@row >= 4, IF(SUBSTITUTE([Work Effort]@row, ",", "~", 4 - 1) = [Work Effort]@row, "", IF(SUBSTITUTE([Work Effort]@row, ",", "~", 4) = [Work Effort]@row, RIGHT([Work Effort]@row, LEN([Work Effort]@row) - FIND(",", SUBSTITUTE([Work Effort]@row, ",", "~", 4 - 1))), MID([Work Effort]@row, FIND("~", SUBSTITUTE([Work Effort]@row, ",", "~", 4 - 1)) + 1, FIND(",", [Work Effort]@row, FIND("~", SUBSTITUTE([Work Effort]@row, ",", "~", 4)) - 1) - FIND(",", [Work Effort]@row, FIND("~", SUBSTITUTE([Work Effort]@row, ",", "~", 4 - 1))) - 1))))
WE5
=IF([Resource Count]@row = 5, (RIGHT([Work Effort]@row, LEN([Work Effort]@row) - (FIND(",", [Work Effort]@row, FIND(",", [Work Effort]@row) + 5)))))
Any help/insight is appreciated!
Thank you!
