SOLVED

Sort by Page # & Sequence

%3CLINGO-SUB%20id%3D%22lingo-sub-2338827%22%20slang%3D%22en-US%22%3ESort%20by%20Page%20%23%20%26amp%3B%20Sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338827%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20Like%20to%20format%20a%20column%20with%20Catalog%20Page%20and%20Item%20Sequence%20number%20that%20sorts%20both%20before%20and%20after%20the%20separator%20instead%20of%20having%20two%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20117.12%2C%20117.13%20Etc.%20where%20117%20is%20the%20page%20number%20and%2012%20indicated%2012th%20item%20on%20page.%20The%20current%20page%20I'm%20working%20on%20has%20103%20items%20on%20page.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20the%20sort%20to%20look%20at%20both%20before%20and%20after%20the%20separator.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20the%20sort%20ends%20up%20with%3A%3C%2FP%3E%3CP%3E117.1%3C%2FP%3E%3CP%3E117.101%3C%2FP%3E%3CP%3E117.11%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20Like%20to%20somehow%20end%20up%20with%3A%3C%2FP%3E%3CP%3E117.1%3C%2FP%3E%3CP%3E117.11%3C%2FP%3E%3CP%3E117.101%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20a%20better%20separator%20to%20use%20to%20have%20these%20results%2C%20or%20a%20way%20to%20keep%20using%20the%20decimal%20point%2C%20I'm%20flexible.%20I'd%20just%20prefer%20to%20keep%20the%20info%20in%20one%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22120%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2263%22%3EHHCSMP8.8%3C%2FTD%3E%3CTD%20width%3D%2257%22%3E117.100%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHHCSMF8.8%3C%2FTD%3E%3CTD%3E117.1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHHCSMF8.8%3C%2FTD%3E%3CTD%3E117.101%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHHCSMF8.8%3C%2FTD%3E%3CTD%3E117.11%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EHHCSMF8.8%3C%2FTD%3E%3CTD%3E117.13%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2338827%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2338869%22%20slang%3D%22en-US%22%3ERe%3A%20Sort%20by%20Page%20%23%20%26amp%3B%20Sequence%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2338869%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042884%22%20target%3D%22_blank%22%3E%40UncleFiat%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAn%20easy%20solution%20would%20be%20to%20pad%20all%20item%20numbers%20to%203%20digits%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20width%3D%22120%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2263%22%3EHHCSMP8.8%3C%2FTD%3E%0A%3CTD%20width%3D%2257%22%3E117.100%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EHHCSMF8.8%3C%2FTD%3E%0A%3CTD%3E117.001%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EHHCSMF8.8%3C%2FTD%3E%0A%3CTD%3E117.101%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EHHCSMF8.8%3C%2FTD%3E%0A%3CTD%3E117.011%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EHHCSMF8.8%3C%2FTD%3E%0A%3CTD%3E117.013%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20values%20will%20then%20be%20sorted%20the%20way%20you%20want%20by%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20would%20be%20to%20record%20the%20page%20number%20and%20item%20number%20in%20separate%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20want%20to%20do%20either%2C%20you%20could%20use%20helper%20columns%3A%3C%2FP%3E%0A%3CP%3EIn%20C2%2C%20enter%20the%20formula%20%3DINT(B2)%3C%2FP%3E%0A%3CP%3EIn%20D2%2C%20enter%20the%20formula%20%3D--MID(B2%2CFIND(%22.%22%2CB2)%2B1%2C3)%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20sort%20on%20columns%20C%20and%20%3CLI-EMOJI%20id%3D%22lia_anguished-face%22%20title%3D%22%3Aanguished_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0389.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279348i0D755DF7830E649E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0389.png%22%20alt%3D%22S0389.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'd Like to format a column with Catalog Page and Item Sequence number that sorts both before and after the separator instead of having two columns.

 

I tried 117.12, 117.13 Etc. where 117 is the page number and 12 indicated 12th item on page. The current page I'm working on has 103 items on page.

 

I'd like the sort to look at both before and after the separator.

 

Currently the sort ends up with:

117.1

117.101

117.11

 

I'd Like to somehow end up with:

117.1

117.11

117.101

 

If there is a better separator to use to have these results, or a way to keep using the decimal point, I'm flexible. I'd just prefer to keep the info in one column.

 

HHCSMP8.8117.100
HHCSMF8.8117.1
HHCSMF8.8117.101
HHCSMF8.8117.11
HHCSMF8.8117.13
2 Replies
best response confirmed by UncleFiat (Occasional Contributor)
Solution

@UncleFiat 

An easy solution would be to pad all item numbers to 3 digits:

 

HHCSMP8.8 117.100
HHCSMF8.8 117.001
HHCSMF8.8 117.101
HHCSMF8.8 117.011
HHCSMF8.8 117.013

 

The values will then be sorted the way you want by Excel.

 

Another would be to record the page number and item number in separate columns.

 

If you don't want to do either, you could use helper columns:

In C2, enter the formula =INT(B2)

In D2, enter the formula =--MID(B2,FIND(".",B2)+1,3)

Fill down.

You can then sort on columns C and

 

S0389.png

 

Thank you, Padding to 3 places