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....

 

[Gsrgofass]

@Gsrgofass
How about

=(RIGHT(F648)&IF(LEN(F648)<6,0,LEFT(F648)))+2000

copied down
[/QUOTE]
Unfortunately, it just displays the formula:

 

[Joe4]

Unfortunately, it just displays the formula:

That means you have formatted the cell as Text, so it is showing the literal formula, as a Text entry.
Change the format of the cell to General and re-enter the formula.

 

[Fluff]

Change the cell format to general & then re-enter the formula.

 

[Gsrgofass]

Sorry, yes it works great. Not terribly important but why the error triangle?

 

[Fluff]

If you select one of those cells & click the ! icon, what does it say?

 

[Gsrgofass]

 

[Fluff]

In that case your data is in a structured table & you don't have the same formula in every cell in that column.

 

[Gsrgofass]

I believe these were the culprits. With these 'funky' serial numbers, it gave the error. I blanked the cells and then recopied the formula down. It clears the error and still allows for those 3 funky serial numbers but it simply displays #VALUE! for 2 and the 3rd cell displays 2000. No error messages anywhere else so this will do just fine.

Thank so much Fluff & Joe4

 

[Fluff]

You can get rid of the error like

=IFERROR((RIGHT(F2)&IF(LEN(F2)<6,0,LEFT(F2)))+2000,"")

We can also get rid of the 2000 value if col F is blank.

 

[Gsrgofass]

It's all good. A few stragglers is totally acceptable. I may even delete those lines since they are dated so long ago.