Forum Discussion

mortimergillian's avatar
mortimergillian
Copper Contributor
May 27, 2025

Issue with number sequencing

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 outsi...
excel
m_tarler's avatar
m_tarler
Bronze Contributor
May 28, 2025

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

Resources