Sort Text Column Containing Only Numeric Data

Posted by: griswald88 on 8 September 2017, 1:25 pm EST

  • Posted 8 September 2017, 1:25 pm EST

    All of my columns are of cell type text, but if all the data in the column is numeric (i.e. "1", "002", "03", "0005", etc), I would like to sort the column as if it were a numeric column and NOT modify the data (i.e. do not change "002" to "2").  I would like a column  containing the following data: "003", "1", "02" to sort as "1", "02", "003".  Does this make sense?  Is there an easy way to accomplish this or do I have to start playing with hidden columns and what not?


     

  • Replied 8 September 2017, 1:25 pm EST

    Hello,


    You can use the AutoSortingColumn event to notify when the sort is started. Then you can check all values to make sure they are numbers. If they are, then change the Comparer used to sort the data.


    Private Sub FpSpread1_AutoSortingColumn(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.AutoSortingColumnEventArgs) Handles FpSpread1.AutoSortingColumn


    Dim numFlag As Boolean = True


    Dim myint As Integer


    For i As Integer = 0 To FpSpread1.Sheets(0).NonEmptyRowCount - 1


    If Integer.TryParse(FpSpread1.Sheets(0).GetValue(i, e.Column), myint) = False Then


    numFlag = False


    Exit For


    End If


    Next


    If numFlag = True Then


    e.Cancel = True


    FpSpread1.Sheets(0).SortRows(e.Column, e.Ascending, e.ShowIndicator, New myComparer)


    End If


    End Sub


     


    Public Class myComparer


    Implements IComparer


    Public Function Compare(ByVal x As Object, ByVal y As Object) As Integer Implements System.Collections.IComparer.Compare


    Dim num1 As Integer = Integer.Parse(x)


    Dim num2 As Integer = Integer.Parse(y)


    If num1 = num2 Then


    Return 0


    End If


    If num1 < num2 Then


    Return -1


    Else


    Return 1


    End If


    End Function


    End Class

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels