Sep 28 2021 11:40 AM
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. I need a column with only the sprint name with the highest number.
Example of the contents of a cell in the column:
Sprint week 2101; Sprint week 2127; Sprint week 2102
(the number is year (21) and week number (01)
Wanted outcome: Sprint week 2127
I need this cleaned data for our planning pivot table that is ordered by sprint and some exported tasks that have been in several sprints or reopened will receive several sprints in this field.
Right now we clean it manually every time we update the data.
I hope someone know a nice formula so i can just add a column with a formula that cleans the imported data.
Regards
Henrik
Sep 28 2021 12:42 PM
That could be
= "Sprint week " &
MAX(
FILTERXML( "<t><s>" &
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(A1, "Sprint week ", ""),
";", "|"
), "|", "</s><s>"
) & "</s></t>",
"//s")
)
Sep 28 2021 11:14 PM
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
Sep 30 2021 03:46 PM
Solution1) 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.
Oct 07 2021 07:29 AM
Oct 07 2021 10:19 AM
You are welcome, glad to help
Sep 30 2021 03:46 PM
Solution1) 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.