Help sorting a combination of text and numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2686068%22%20slang%3D%22en-US%22%3EHelp%20sorting%20a%20combination%20of%20text%20and%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686068%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20an%20example%20of%20a%20cell.%20I%20essentially%20want%20to%20sort%20a%20data%20file%20of%20over%202000%20entries%20by%20just%20the%20BCL-6%20entries%20using%20the%20H%20Score%20values%20(in%20bold).%20So%20all%20entries%20above%20and%20below%20a%20cutoff%20of%20H%20score%201.4.%20Note%20that%20because%20H%20score%20is%20also%20used%20with%20another%20stain%2C%20I%20have%20to%20specify%20which%20H%20score.%20Is%20there%20a%20formula%20I%20can%20write%20to%20help%20me%20with%20this%3F%20The%20example%20shown%20is%20one%20cell%20is%20a%20database%20with%208%20columns.%20Thanks%20for%20any%20help!%3C%2FP%3E%3CTABLE%20width%3D%22461%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22461%22%3EBETA%20INTEGRIN%20STAIN%3A%26nbsp%3B%20H%20SCORE%3A%26nbsp%3B%201.8%26nbsp%3B%26nbsp%3B%20POSITIVE%2F%20NORMAL%20RESULT.%20%3CSTRONG%3EBCL-6%20STAIN%3A%26nbsp%3B%20H%20SCORE%3A%26nbsp%3B%203.8%3C%2FSTRONG%3E%26nbsp%3B%26nbsp%3B%20POSITIVE%2F%20ABNORMAL%20RESULT.%20INTERPRETATION%3A%20THE%20BETA-3%20INTEGRIN%20EXPRESSION%20IS%20NORMAL%20AND%20THE%20ENDOMETRIUM%20IS%26nbsp%3B%22IN-PHASE%22%20BUT%20BCL-6%20EXPRESSION%20IS%20ABNORMAL%20SUGGESTING%20THE%20PRESENCE%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20OF%20ENDOMETRIOSIS%20OR%20HYDROSALPINGES.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%C2%B7%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2686068%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-2686434%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20sorting%20a%20combination%20of%20text%20and%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2686434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137783%22%20target%3D%22_blank%22%3E%40Angress%3C%2FA%3E%26nbsp%3BPerhaps%20something%20like%20in%20the%20attached%20file.%20Not%20very%20elegant%20but%20it%20could%20work%20if%20the%20wording%20and%20spacing%20of%20%22%3CSTRONG%3EBCL-6%20STAIN%3A%20H%20SCORE%3A%26nbsp%3B%3C%2FSTRONG%3E%22%20is%20always%20the%20same.%3C%2FP%3E%3CP%3EHave%20added%20a%20few%20records%20and%20juggled%20the%20text%20around%20a%20bit%20to%20test%20the%20formula.%20Seems%20to%20work.%20See%20attached.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-26%20at%2007.52.13.png%22%20style%3D%22width%3A%20672px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305939iAB395C879016D3D0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-26%20at%2007.52.13.png%22%20alt%3D%22Screenshot%202021-08-26%20at%2007.52.13.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Here is an example of a cell. I essentially want to sort a data file of over 2000 entries by just the BCL-6 entries using the H Score values (in bold). So all entries above and below a cutoff of H score 1.4. Note that because H score is also used with another stain, I have to specify which H score. Is there a formula I can write to help me with this? The example shown is one cell is a database with 8 columns. Thanks for any help!

BETA INTEGRIN STAIN:  H SCORE:  1.8   POSITIVE/ NORMAL RESULT. BCL-6 STAIN:  H SCORE:  3.8   POSITIVE/ ABNORMAL RESULT. INTERPRETATION: THE BETA-3 INTEGRIN EXPRESSION IS NORMAL AND THE ENDOMETRIUM IS "IN-PHASE" BUT BCL-6 EXPRESSION IS ABNORMAL SUGGESTING THE PRESENCE      OF ENDOMETRIOSIS OR HYDROSALPINGES.


·     
3 Replies

@Angress Perhaps something like in the attached file. Not very elegant but it could work if the wording and spacing of "BCL-6 STAIN: H SCORE: " is always the same.

Have added a few records and juggled the text around a bit to test the formula. Seems to work. See attached.

Screenshot 2021-08-26 at 07.52.13.png

 

@Riny_van_Eekelen 

 

Riny,

THank you so much. I tried applying your formula in the test file but I am getting a value error (see attached). I did not change anything. Just took a new data field and tried applying. Could it be a formatting error on my part?

 

Dan

 

@Angress As mentioned, the wording and spacing has to be exactly the same for all occurrences of "BCL-6 STAIN: H SCORE: " . In my example, the string to look for should be exactly like that, i.e. 22 characters long.

 

In the two examples you added the first one has an extra space between the ":" and the "H"

"BCL-6 STAIN:  H SCORE: "

 

And in the second example you have two space like above and a hyphen between "H" and "score"

"BCL-6 STAIN:  H-SCORE: "

 

If there is no way to make this consistent, it's going to be difficult to make it work. But perhaps you can adapt the formula to you real world scenario and make sure that the data is consistent.