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


    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


    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


    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