Forum Discussion
Solved
Selecting sprint with highest number from a string in a cell
Hi I hope someone in this community can help me improve a repetitive task for our project managers. Right to the challenge I'm facing: I have a cell that can contain one or more sprint names...
1) That's since SUBSTITUTE() within the formula is case sensitive, I changed on using lowercase text to ignore characters case.
2) Formulas in Excel can't return blank values, here we return empty string "" if Sprint Week text is not within text in cell.
= IF( ISNUMBER(SEARCH("sprint week",E2)), "Sprint week " & MAX( FILTERXML( "<t><s>" & SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(LOWER(E2), "sprint week ", ""), ";", "|" ), "|", "</s><s>" ) & "</s></t>", "//s") ), "")Please check in attached.
Hi Sergei,
It simply works perfectly. Its black magic to me, but it does exactly what I needed.
Thanks a million.
Regards
Henrik
It simply works perfectly. Its black magic to me, but it does exactly what I needed.
Thanks a million.
Regards
Henrik
You are welcome, glad to help