Forum Discussion
Selecting sprint with highest number from a string in a cell
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.
That could be
= "Sprint week " &
MAX(
FILTERXML( "<t><s>" &
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A1, "Sprint week ", ""),
";", "|"
), "|", "</s><s>"
) & "</s></t>",
"//s")
)Hi Sergei,
Wow, this is so many levels over my OK Excel skills.
I used the formula and found the following problems for taking it in to use:
1. Somehow it returns Sprint week 0 when added to the actual export. So I will add this file to the thread for you to see.
2. If it contains 1 value it should print this value in the field as I need it to be this column I add to my pivot table with all our sprints and how many hours are in them.
3. if it contains no sprint value it should just be empty.I hope you can help
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
