Forum Discussion
Sort by Page # & Sequence
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.8 | 117.100 |
| HHCSMF8.8 | 117.1 |
| HHCSMF8.8 | 117.101 |
| HHCSMF8.8 | 117.11 |
| HHCSMF8.8 | 117.13 |
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 😧
2 Replies
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 😧
- Thank you, Padding to 3 places
