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.

    <FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT><FONT size=2> FpSpread1_AutoSortingColumn(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> sender </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Object</FONT></FONT><FONT size=2>, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> e </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.AutoSortingColumnEventArgs) </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Handles</FONT></FONT><FONT size=2> FpSpread1.AutoSortingColumn


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> numFlag </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Boolean</FONT></FONT><FONT size=2> = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> myint </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>For</FONT></FONT><FONT size=2> i </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT size=2> = 0 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>To</FONT></FONT><FONT size=2> FpSpread1.Sheets(0).NonEmptyRowCount - 1


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT size=2>.TryParse(FpSpread1.Sheets(0).GetValue(i, e.Column), myint) = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>False</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Then

    </FONT></FONT><FONT size=2>

    numFlag = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>False

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Exit</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>For

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Next

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If</FONT></FONT><FONT size=2> numFlag = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Then

    </FONT></FONT><FONT size=2>

    e.Cancel = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True

    </FONT></FONT><FONT size=2>

    FpSpread1.Sheets(0).SortRows(e.Column, e.Ascending, e.ShowIndicator, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> myComparer)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Sub</FONT></FONT>


    <FONT color=#0000ff size=2><FONT color=#0000ff size=2> 

    </FONT></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    Public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Class</FONT></FONT><FONT size=2> myComparer


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Implements</FONT></FONT><FONT size=2> IComparer


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Public</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Function</FONT></FONT><FONT size=2> Compare(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> x </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Object</FONT></FONT><FONT size=2>, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ByVal</FONT></FONT><FONT size=2> y </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Object</FONT></FONT><FONT size=2>) </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Implements</FONT></FONT><FONT size=2> System.Collections.IComparer.Compare


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> num1 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT size=2> = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT size=2>.Parse(x)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> num2 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT size=2> = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Integer</FONT></FONT><FONT size=2>.Parse(y)


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If</FONT></FONT><FONT size=2> num1 = num2 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Then

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Return</FONT></FONT><FONT size=2> 0


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If</FONT></FONT><FONT size=2> num1 < num2 </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Then

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Return</FONT></FONT><FONT size=2> -1


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Else

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Return</FONT></FONT><FONT size=2> 1


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>If

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Function


    End</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Class

    </FONT></FONT>
Need extra support?

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

Learn More

Forum Channels