String Field sort 3

[zsyed]

Wondering how to sort a string field to put them in the order of sequence. the field follows following format.
XXYY-ZZZZ
where
XX - last two digits of Year
YY - two digits of month.
ZZZZ - numeric sequence.

Example data. (in this 900 sequence to bring in before 1000).
1409‑1154
1409‑1212
1409‑1220
1411‑1000
1411‑1009
1411‑1016
1411‑1020
1411‑1025
1411‑1028
1411‑1034
1411‑1037
1411‑1038
1411‑1040
1411‑940
1411‑972
1411‑974
1411‑976
1411‑977
1411‑981
1411‑982
1411‑985
1411‑986
1411‑987
1411‑988


Thanks and Regards,
Zaheer.

 

[SkipVought]

Hi,

Troubling is that your format is NOT XXYY-ZZZZ!!!!

You've broken the format with XXYY-ZZZ!

To maintain XXYY-ZZZZ...
[tt]
1411-0940
[/tt]

 

[kray4660]

The only way I can think of doing it is to make a formula to strip out the '-', then add a zero to numbers less than 1000 and sort on that formula

Something like this.

left({somefield}, 4)+(if tonumber(mid({somefield},instr({somefield},'-')+1)) <1000 then '0'+{somefield} else {somefield})

FYI: I have not tested the code and it only will work with numbers number to the right of the dash >=100.

 

[zsyed]

Thank you for both replies. I took the suggestion to have correct format of serial number for future generation of serial numbers. And, the formula field to sort the existing data.

Thank you very much.

Regards,
Zaheer.

 

[Charliy]

(val(split({field},'-')[1])*10000)
+ val(split({field},'-')[2]

or

split({field})[1]
&
totext(val(split({field},'-')[2}),"0000")