Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Hello,
    I have a spreadsheet that has values with multiple decimal places see below:
    1.2.1.5
    2.1
    1.35
    1.2.5
    2.587
    2.3.9.8

    How can I get Excel to sort all those that start with 1 and then those that start with 2 so my sorted list would look like this:
    1.2.1.5
    1.2.5
    1.35
    2.1
    2.3.9.8
    2.587

    Any assistance you can provide is greatly appreciated,
    MOstate

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Select your data, select Data, Sort, press OK and tell Excel to sort everything as text.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Sorry I don't see an option in Excel 2003 to sort at text. I formatted the data as text and then did a Data... Sort and it didn't work.
    Thanks,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create this function in a module in the Visual Basic Editor:

    Code:
    Function FormatText(strValue As String, Optional intMaxDigits As Integer = 3) As String
      Dim arrParts As Variant
      Dim i As Integer
      arrParts = Split(strValue, ".")
      For i = LBound(arrParts) To UBound(arrParts)
        FormatText = FormatText & "." & Format(arrParts(i), String(intMaxDigits, "0"))
      Next i
      If Not FormatText = "" Then
        FormatText = Mid(FormatText, 2)
      End If
    End Function
    Let's say your data are in A1:A5.
    Enter the following formula in B1:

    =FormatText(A1)

    Fill down to B5.
    Click in cell B1, then sort.

    If you have more than 3 digits between periods, change the formula to

    =FormatText(A1,5)

    if you have a maximum of 5 digits between periods.

    See the attached sample workbook.

    [attachment=87186:FormatSample.xls]
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    HansV
    Thanks for the reply I did copy the module and got the text format to work but the sort order still isn't correct. Please see how it sorted below:
    1.35 001.035
    2.1 002.001
    2.587 002.587
    1.2.1.5 001.002.001.005
    1.2.5 001.002.005
    2.3.9.8 002.003.009.008

    Thanks!!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It sorts correctly when I try it...

  7. #7
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Would you mind taking a look at my spreadsheet and see if you find my problem?

    Thanks
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is the sort order I received after opening your file - no alterations on my part other than clicking on a value in the B column and pressing the sort A to Z button. Is this the correct order?


    00001.00002
    00001.00002.00003
    00001.00005
    00001.00018.00009
    00002.00001
    00002.00001.00005

  9. #9
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Your original question and your file have two different sets of numbers.

    Here is a sort order I get for your original question ...

    Sorted
    1.2.1.5
    1.2.5
    1.35
    2.1
    2.3.9.8
    2.587

    Here is what I can get from the numbers in your file
    1.18.9
    1.2
    1.2.3
    1.5
    2.1
    2.1.5

    See column C formula in the attached. I then Copied col. C and Paste Special Values in col D. Then sort col D.
    You could also Copy col C and Paste Special Values into col A.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Not sure what's going on because when I sort by column D here is my sort order results. I have attached the workbook as well.
    Thanks

    1.2
    1.5
    2.1
    1.18.9
    1.2.3
    2.1.5
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The values in column D have nothing to do with those in column A, so sorting on column B (with the FormatText formulas), column D will not be sorted in any specific order.
    If you want to sort column D the way you want, create FormatText formulas such as =FormatText(D1) in - say - column E, and sort on column E. See attached version.

    [attachment=87194:my copy of sample.xls]
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - Take a look at Sheet 3. Do you get that Warning? If so, choose the second one for your sort.
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Dec 2009
    Location
    Akron, OH, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Would you mind helping a newbie who obviously missed something here?

    I created a sample spreadsheet.
    I opened the VB editor and inserted a module.
    I copied the FormatText function into the module and saved it.
    I entered the formula =FormatText(A1) in B1

    Got #NAME? error.

    I have the correct spelling in both the module and the formula.
    Any hints on what I might have done wrong?
    I'm sure it's a really basic error on my part.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Which version of Excel are you using?

    (If you wish, you can attach your workbook to a reply:
    - Click Browse... below the area where you type your reply.
    - Select your workbook (or a copy of it with personal information removed) and click Open.
    - Click Upload file.
    - Click Add to post.
    That would enable others to see what goes wrong, hopefully)

  15. #15
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is it in the same workbook that you are trying to use the formula? Make sure the you didn't add the module to the wrong book. Also if macros are not enabled, you will receive the error.

Page 1 of 2 12 LastLast

Posting Permissions

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