Issue with number sequencing

Question

Hello, I am having an issue with number sequencing. I have a spreadsheet where Column B is used as a number tracking column, starting at S25.001 to S25.999. A few days ago there were rows added outside of this sequence.

For example, everything is fine to S25.100. Then the next row is S25.1000. This then goes row by row to S25.1019 and then goes back to S25.101.

I thought a simple filter would correct it but have tried multiple ways and cannot re-arrange the numbers. Thanks in advance for any support on this issue.

hansvogelaar · Answer

Perhaps change S25.nnn to S25.0nnn

jundiyaalhaqiqi · Answer

&nbsp;This will create sequence from 1 to 999Custom Format Cell as original (000 to make it a 3-digit number). Change to 0000 to make it a 4-digit number.&nbsp;&nbsp;

m_tarler · Answer

The problem is that "S25.001" is treated as TEXT and therefore it will be sorted as TEXT not numberically.

Suggestion by Hans to redo your numbers to give another character space so you can correctly sort up to 9999 instead of having a problem after 999. i.e. you will have S25.0999 and then S25.1000

Suggestion by JundiyaAlHaqiqi is to get rid of the "S25." part of the text and only count actual numbers 1, 2, 3, .... to nnnn. Then since the values in the cells will be NUMBERS they will sort correctly but the special Number Formatting shown above will DISPLAY those values the was you wanted to SEE them (note the actual values in those cells will be number NOT the "S25.001" you see).

Yet another option would be to add a helper column with either

=--TEXTAFTER(A1:A9999, ".")

or in older Excel

=--MID(A1, 5,99)

in either case this will give you the NUMBER that follows the decimal point and you can sort using that helper column