Extracting numbers separated by commas from a cell
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!
Best Answer

@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.
Answers

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," ")

@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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!