# Sequential serial numbers do not come up in sequence

Copper Contributor

# Sequential serial numbers do not come up in sequence

I have a number of areas for whom I raise Penalty Notices, and want them to show in sequntial order.  When i trip over 9 into 10, it then sequences after the #1 number, and not after the #9 number.  How do i get the sequence to continue??

4 Replies

# Re: Sequential serial numbers do not come up in sequence

@Phil_Gibbs That's because you are sorting texts. "FORW11" comes before "FORW2".

I you want the text sorting to be correct, change the codes so that the have the same number of numbers at the end using leading zeroes. Then "FORW002" will sort before "FORW011".

# Re: Sequential serial numbers do not come up in sequence

A formula that could prove useful is

``````= LET(
seq,   VALUE(MID(areaCode, 5, 2)),
area,  LEFT(areaCode, 4),
area & TEXT(seq, "00")
)``````

That would create a calculated list of area codes of the form FORW02 (as suggested by @Riny_van_Eekelen ) which could either be used in dependent formulas or to Copy / Paste values over the originals.

Equally, the formula

``````= LET(
seq,  VALUE(MID(areaCode, 5, 2)),
area, LEFT(areaCode, 4),
SORTBY(records, area, 1, seq, 1)
)``````

would create an output that is sorted on the sequence digits as numbers.

# Re: Sequential serial numbers do not come up in sequence

all sorted, thank you

# Re: Sequential serial numbers do not come up in sequence

If with sql,

//select * from basic_order_by_natural;
select * from basic_order_by_natural order by number COLLATE NATURAL_CMP ;