Sort number data within a cell
Results 1 to 5 of 5

Thread: Sort number data within a cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sort number data within a cell

    Sorting Data (Numbers) Within a Single Cell

    I'm using a spreadsheet to input a series of digits into a particular cell. I want to be able to use formula 。
    For example, I input the numbers: 21, 10, 37, 2, 5, 44 into cell A1; the numbers will be separted by a space not a comma (,).
    After formula, cell B2 (or whatever cell) should show: 2 5 10 21 37 44. Please help!

    if I input the numbers: 21,21, 10, 37, 2, 2,5, 44,37 into cell A1, the B1 should also cannot duplicated ,so should still be 2 5 10 21 37 44


    Regards.

    i use the url https://www.ozgrid.com/forum/forum/h...-a-single-cell
    but the different is
    1.to Remove duplicate numbers
    2.use real formula not vba
    3.if use VBA how to?
    thanks a lot

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,999
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Sort number data within a cell

    Here is a VBA solution... it is a UDF (user defined function)... see installation instructions below:
    Code:
    Function SortNumbers(Text As String) As String
      Dim X As Long, Max As Long, Data As Variant
      Text = Replace(Application.Trim(Replace(Text, ",", " ")), " ", ",")
      Max = Evaluate("MAX(" & Text & ")")
      Data = Split(Text, ",")
      With CreateObject("System.Collections.ArrayList")
        For X = 0 To UBound(Data)
          .Add Application.Trim(Format(Data(X), String(Len(Max), "0")))
        Next
        .Sort
        Data = .ToArray
      End With
      With CreateObject("Scripting.Dictionary")
        For X = 0 To UBound(Data)
          .Item(CLng(Data(X))) = 1
        Next
        SortNumbers = Join(.keys)
      End With
    End Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SortNumbers just like it was a built-in Excel function. For example, if cell A1 contains your number list, then put this in cell B1...

    =SortNumbers(A1)

    If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort number data within a cell

    thanks!

    it s run perfect !

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,328
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Sort number data within a cell

    Just another UDF

    Code:
    Function Sorting_Num(r As Range)
        Dim n As New Collection, e As Boolean, cad As String, i As Long, c As Variant
        On Error Resume Next
        For Each c In Application.Trim(Split(r.Value, ","))
            e = False
            For i = 1 To n.Count
                If Val(c) < n(i) Then n.Add Val(c), c, i: e = True
            Next
            If e = False Then n.Add Val(c), c
        Next
        For i = 1 To n.Count
            cad = cad & n(i) & " "
        Next
        Sorting_Num = Left(cad, Len(cad) - 1)
    End Function
    Regards Dante Amor

  5. #5
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,375
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sort number data within a cell

    Yet another way.

    Code:
    Function Sorted(val As String) As String
    val = Replace(val, " ", "")
    Dim s() As String: s = Split(val, ",")
    Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
    Dim tmp As Long
    
    For i = LBound(s) To UBound(s)
        tmp = s(i)
        If Not AL.contains(tmp) Then AL.Add tmp
    Next i
    
    AL.Sort
    Sorted = Join(AL.toArray, " ")
    
    End Function
    Last edited by lrobbo314; Jul 21st, 2019 at 01:44 AM. Reason: formatting
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •