Numeric Sort with Trailing Zeros

[Gsrgofass]

Hello,

I have a serial number column in my database where the leading and last number of the 6 digit number represents the YOM (year of manufacture) of the machine. I have it formatted as 'Special' in order to display a leading zero for 2020 models.
When doing a general search for a range of serial numbers choosing 'Smallest to Largest' is a popular choice.
Ex:
019742 (2020 YOM)
793821 (2017 YOM)
803061 (2018 YOM)
915471 (2019 YOM)

If I sort 'Smallest to Largest', I get the order as you see above. 2020 listed prior to any other year. When 2021 hits, I will have years 2019 and then see 2021 serial number with 2020 still at the top of the list.

Is it possible to sort but have the leading 0 come after a leading 9 and then allow the next leading number to be a 1, 2.... and so on?
793821 (2017 YOM)
803061 (2018 YOM)
915471 (2019 YOM)
019742 (2020 YOM)
115202 (2021 YOM)
213302 (2022 YOM)
etc....

 

[Peter_SSs]

Are you going to be doing the sort manually or will it be done by a macro?

In the example so far, the earliest year is 2017 and the latest 2022 but what will be the absolute earliest and latest years to sort?

 

[Joe4]

One way would be to extract the year part in another column with a formula, and use this new column as your Primary sort field, and then your values above as your Secondary sort field.

 

[Gsrgofass]

Are you going to be doing the sort manually or will it be done by a macro?

In the example so far, the earliest year is 2017 and the latest 2022 but what will be the absolute earliest and latest years to sort?

Sort in manual. The earliest year in the database is 2013 and I would like to keep all the data in one sheet, if possible.
This database will continue to grow as the years go on so the latest year will continue to increase.

 

[Gsrgofass]

One way would be to extract the year part in another column with a formula, and use this new column as your Primary sort field, and then your values above as your Secondary sort field.

As long as the added column does not affect the other formulas I have in the workbook. Do you have a formula in mind that I could try?

 

[Peter_SSs]

The earliest year in the database is 2013

In that case I think you will need something like Joe suggested otherwise 2013 and 2023 are likely to get sorted together if you are just using the first digit.

Here is a possibility. Use this formula then sort both columns together using column B as the sorting column.

20 09 10.xlsm
	A	B
1
2	793821	17
3	803061	18
4	915471	19
5	019742	20
6	115202	21
7	213302	22
8	365332	23
9	365331	13
Sort Years

Cell Formulas
Range	Formula
B2:B9	B2	=(RIGHT(A2,1)&LEFT(A2,1))+0

 

[Peter_SSs]

.., or if the data does contain the parentheses as well

20 09 10.xlsm
	A	B
1
2	793821 (2017 YOM)	2017
3	803061 (2018 YOM)	2018
4	915471 (2019 YOM)	2019
5	019742 (2020 YOM)	2020
6	115202 (2021 YOM)	2021
7	213302 (2022 YOM)	2022
8	365332 (2023 YOM)	2023
9	365331 (2013 YOM)	2013
Sort Years

Cell Formulas
Range	Formula
B2:B9	B2	=MID(A2,9,4)+0

 

[Gsrgofass]

.., or if the data does contain the parentheses as well

No info in brackets. I just did that to illustrate the year. The cells only contain the 6 digit number.

 

[Gsrgofass]

I added the formula to cell G2 but changed it to =(RIGHT(F2,1)&LEFT(F2,1))+0 and it did not work. Sorry, newbie at Excel here.
Am I missing something? Do the cells in column G needs to be a specific format? They are 'General' as default.

 

[Gsrgofass]

Also, note the cells I highlighted in RED. Can you also advise how to format the cells that when a serial number is repeated in that column, it highlights those cells RED automatically?