Problem with IF CONTAINS formula with multiple IF

Heather Hall

I am trying to return a value of 1 through 26 based on the corresponding number in a column (Sprint column). It works when I just use one set, but not all of them. I realize this formula is huge so if there is a better way to do this, please feel free to suggest. The number convention of the column contains a value and dates like 22.01 1/11/-1/24, 22.02 1/25-2/7, etc so I can't use =, CONTAINS seems to be the best option.

=IF(CONTAINS(".01", Sprint@row), "1", IF(CONTAINS(".02", Sprint@row), "2", IF(CONTAINS(".03", Sprint@row), "3", IF(CONTAINS(".04", Sprint@row), "4", IF(CONTAINS(".05", Sprint@row), "5", IF(CONTAINS(".06", Sprint@row), "6", IF(CONTAINS(".07", Sprint@row), "7", IF(CONTAINS(".08", Sprint@row), "8", IF(CONTAINS(".09", Sprint@row), "9", IF(CONTAINS(".10", Sprint@row), "10", IF(CONTAINS(".11", Sprint@row), "11", IF(CONTAINS(".12", Sprint@row), "12", IF(CONTAINS(".13", Sprint@row), "13", IF(CONTAINS(".14", Sprint@row), "14", IF(CONTAINS(".15", Sprint@row), "15", IF(CONTAINS(".16", Sprint@row), "16", IF(CONTAINS(".17", Sprint@row), "17", IF(CONTAINS(".18", Sprint@row), "18”, IF(CONTAINS(".19", Sprint@row), "19", IF(CONTAINS(".20", Sprint@row), "20", IF(CONTAINS(".21", Sprint@row), "21", IF(CONTAINS(".22", Sprint@row), "22", IF(CONTAINS(".23", Sprint@row), "23", IF(CONTAINS(".24", Sprint@row), "24", IF(CONTAINS(".25", Sprint@row), "25", IF(CONTAINS(".26", Sprint@row), "26")))

Paul Newcome

Lets try this:

=MID(Sprint@row, FIND(".", Sprint@row) + 1, 2)

Paul Newcome

Exactly what is in the Sprint column? Is it just the ".24" or is there additional text included? Are you able to provide a screenshot for reference? It may very well be possible to simplify this greatly, but that is going to depend on your structure and data.

Heather Hall

No it has a year and number as well as a date range.

Paul Newcome

Lets try this:

=MID(Sprint@row, FIND(".", Sprint@row) + 1, 2)

Heather Hall

Yes, that worked! Thank you so much.

Paul Newcome

Happy to help. 👍️