SOLVED

Number Sorting is not working

%3CLINGO-SUB%20id%3D%22lingo-sub-2119265%22%20slang%3D%22en-US%22%3ENumber%20Sorting%20is%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119265%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20sheet%20%2C%20I%20am%20not%20able%20to%20sort%20Column%20B%20from%20low%20to%20hight%20in%205026ADDLoad(sorting)tab.%20It%20used%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2119265%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119550%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20Sorting%20is%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119550%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20it%20was%20set%20this%20option%20on%20sorting%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20326px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F253727i3062E3470EDAB277%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMore%20about%20sorting%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fsort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESort%20data%20in%20a%20range%20or%20table%20-%20Excel%20(microsoft.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119455%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20Sorting%20is%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119455%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20%22D%3D26-48%2C%20L%3D50ft(WN%20Joists)%22%26nbsp%3B%20column%20B%20.%20i%20did%20it%20few%20weeks%20ago.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119444%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20Sorting%20is%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%26nbsp%3BWhich%20column%20is%20your%20formula%20in%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119279%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20Sorting%20is%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119279%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BWhat%20i%20am%20not%20sure%20about%20how%20it%20worked%20in%20a%20different%20sheet(%20attached)%20without%20using%20%22VALUE%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119274%22%20slang%3D%22en-US%22%3ERe%3A%20Number%20Sorting%20is%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351459%22%20target%3D%22_blank%22%3E%40SamFares%3C%2FA%3E%26nbsp%3BYou%20have%20used%20the%20MID%20function.%20That%20makes%20everything%20text.%20And%20your%20column%20B%20is%20sorted%20as%20text.%3C%2FP%3E%3CP%3EUse%20this%20in%20stead%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVALUE(MID(%5B%40Mark%5D%2C2%2C3))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hello All,

 

In the attached sheet , I am not able to sort Column B from low to hight in 5026ADDLoad(sorting)tab. It used to work.

 

Thanks,

Sam

5 Replies
Best Response confirmed by SamFares (Contributor)
Solution

@SamFares You have used the MID function. That makes everything text. And your column B is sorted as text.

Use this in stead:

=VALUE(MID([@Mark],2,3))

 

Thank you @Riny_van_Eekelen !

 

@Riny_van_Eekelen What i am not sure about how it worked in a different sheet( attached) without using "VALUE"

@SamFares Which column is your formula in?

@Riny_van_Eekelen  "D=26-48, L=50ft(WN Joists)"  column B . i did it few weeks ago. 

@SamFares 

Most probably it was set this option on sorting

image.png

More about sorting Sort data in a range or table - Excel (microsoft.com)