Search code examples
crystal-reports

crystal report formula sort string numerically

i got a column of strings and i'm trying to sort it in crystal report

103
102
101-3
101-1
101-2
919
213-2
213-1A
111
213-1B

and i want to sort it this way

101-1
101-2
101-3
102
103
111
213-1A
213-1B
213-2
919

i've tried but with no luck

if IsNumeric({TableName.TextNum}) = false
then "0" & {TableName.TextNum} 
else NumericText(right({TableName.TextNum}, 1) 
then {TableName.TextNum}

glad with any help. tnx :)

Solution

  • Assuming their are no alphabets before -. Try this:

    Create a formula and write below code:

    ToNumber(Split(databasefield,'-')[1])

    Use the above formula to sort, if not possible then create a group with the formula and then sort the group ascending