SOLVED

Selecting sprint with highest number from a string in a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2792878%22%20slang%3D%22en-US%22%3ESelecting%20sprint%20with%20highest%20number%20from%20a%20string%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2792878%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20someone%20in%20this%20community%20can%20help%20me%20improve%20a%20repetitive%20task%20for%20our%20project%20managers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ERight%20to%20the%20challenge%20I'm%20facing%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20have%20a%20cell%20that%20can%20contain%20one%20or%20more%20sprint%20names.%20I%20need%20a%20column%20with%20only%20the%20sprint%20name%20with%20the%20highest%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EExample%20of%20the%20contents%20of%20a%20cell%20in%20the%20column%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESprint%20week%202101%3B%20Sprint%20week%202127%3B%20Sprint%20week%202102%3C%2FP%3E%3CP%3E(the%20number%20is%20year%20(21)%20and%20week%20number%20(01)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWanted%20outcome%3A%20Sprint%20week%202127%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20this%20cleaned%20data%20for%20our%20planning%20pivot%20table%20that%20is%20ordered%20by%20sprint%20and%20some%20exported%20tasks%20that%20have%20been%20in%20several%20sprints%20or%20reopened%20will%20receive%20several%20sprints%20in%20this%20field.%3C%2FP%3E%3CP%3ERight%20now%20we%20clean%20it%20manually%20every%20time%20we%20update%20the%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20someone%20know%20a%20nice%20formula%20so%20i%20can%20just%20add%20a%20column%20with%20a%20formula%20that%20cleans%20the%20imported%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EHenrik%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2792878%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2793047%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20sprint%20with%20highest%20number%20from%20a%20string%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2793047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1169220%22%20target%3D%22_blank%22%3E%40H_Lambert%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20%22Sprint%20week%20%22%20%26amp%3B%0A%20%20%20MAX(%0A%20%20%20%20%20FILTERXML(%20%22%3CT%3E%3CS%3E%22%20%26amp%3B%0A%20%20%20%20%20%20SUBSTITUTE(%0A%20%20%20%20%20%20%20%20%20SUBSTITUTE(%0A%20%20%20%20%20%20%20%20%20%20%20SUBSTITUTE(A1%2C%20%22Sprint%20week%20%22%2C%20%22%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%3B%22%2C%20%22%7C%22%0A%20%20%20%20%20%20%20%20)%2C%20%22%7C%22%2C%20%22%3C%2FS%3E%3CS%3E%22%0A%20%20%20%20%20%20%20)%20%26amp%3B%20%22%3C%2FS%3E%3C%2FT%3E%22%2C%0A%20%20%20%20%20%22%2F%2Fs%22)%0A%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2794091%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20sprint%20with%20highest%20number%20from%20a%20string%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2794091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3EWow%2C%20this%20is%20so%20many%20levels%20over%20my%20OK%20Excel%20skills.%3C%2FP%3E%3CP%3EI%20used%20the%20formula%20and%20found%20the%20following%20problems%20for%20taking%20it%20in%20to%20use%3A%3CBR%20%2F%3E1.%20Somehow%20it%20returns%20Sprint%20week%200%20when%20added%20to%20the%20actual%20export.%20So%20I%20will%20add%20this%20file%20to%20the%20thread%20for%20you%20to%20see.%3CBR%20%2F%3E2.%20If%20it%20contains%201%20value%20it%20should%20print%20this%20value%20in%20the%20field%20as%20I%20need%20it%20to%20be%20this%20column%20I%20add%20to%20my%20pivot%20table%20with%20all%20our%20sprints%20and%20how%20many%20hours%20are%20in%20them.%3CBR%20%2F%3E3.%20if%20it%20contains%20no%20sprint%20value%20it%20should%20just%20be%20empty.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2801218%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20sprint%20with%20highest%20number%20from%20a%20string%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2801218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1169220%22%20target%3D%22_blank%22%3E%40H_Lambert%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20That's%20since%20SUBSTITUTE()%20within%20the%20formula%20is%20case%20sensitive%2C%20I%20changed%20on%20using%20lowercase%20text%20to%20ignore%20characters%20case.%3C%2FP%3E%0A%3CP%3E2)%20Formulas%20in%20Excel%20can't%20return%20blank%20values%2C%20here%20we%20return%20empty%20string%20%22%22%20if%20Sprint%20Week%20text%20is%20not%20within%20text%20in%20cell.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20IF(%20ISNUMBER(SEARCH(%22sprint%20week%22%2CE2))%2C%0A%20%22Sprint%20week%20%22%20%26amp%3B%0A%20%20%20MAX(%0A%20%20%20%20%20FILTERXML(%20%22%3CT%3E%3CS%3E%22%20%26amp%3B%0A%20%20%20%20%20%20SUBSTITUTE(%0A%20%20%20%20%20%20%20%20%20SUBSTITUTE(%0A%20%20%20%20%20%20%20%20%20%20%20SUBSTITUTE(LOWER(E2)%2C%20%22sprint%20week%20%22%2C%20%22%22)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%3B%22%2C%20%22%7C%22%0A%20%20%20%20%20%20%20%20)%2C%20%22%7C%22%2C%20%22%3C%2FS%3E%3CS%3E%22%0A%20%20%20)%20%26amp%3B%20%22%3C%2FS%3E%3C%2FT%3E%22%2C%0A%20%20%20%20%22%2F%2Fs%22)%20)%2C%0A%20%20%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPlease%20check%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

5 Replies

@H_Lambert 

That could be

= "Sprint week " &
   MAX(
     FILTERXML( "<t><s>" &
      SUBSTITUTE(
         SUBSTITUTE(
           SUBSTITUTE(A1, "Sprint week ", ""),
            ";", "|"
        ), "|", "</s><s>"
       ) & "</s></t>",
     "//s")
   )

@Sergei Baklan 

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

best response confirmed by H_Lambert (New Contributor)
Solution

@H_Lambert 

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

@H_Lambert 

You are welcome, glad to help