Results 1 to 15 of 23

20091216, 11:51 #1
 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

20091216, 11:58 #2
 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.jkpads.com
Professional Office Developers Association

20091216, 12:03 #3
 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,

20091216, 12:06 #4
 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
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]

20091216, 12:29 #5
 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!!

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

20091216, 12:37 #7
 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

20091216, 13:24 #8
 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

20091216, 13:40 #9
 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.

20091216, 14:08 #10
 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

20091216, 14:15 #11
 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]

20091216, 14:21 #12
 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.

20091216, 15:09 #13
 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.

20091216, 15:14 #14
 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)

20091216, 15:51 #15
 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.