Extracting numbers separated by commas from a cell

SteveE

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!

SteveE

@JamesB Hi James,

They will not be static.

And I just realized my mistake. I got confused in the middle and placed a "," when I should have placed "~"

User error.

JamesB

@SteveE

Will your work effort Numbers in the Work Effort column always be static 15,2,4,10?

If so you can use an =IF(Contains(15,[Work Effort]@row),15," ") in your WE1 and then just update as necessary for the rest.

=IF(Contains(2,[Work Effort]@row),2," ")

SteveE

@JamesB Hi James,

They will not be static.

And I just realized my mistake. I got confused in the middle and placed a "," when I should have placed "~"

User error.