# Thread: Sort with multiple decimal places

1. 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. Select your data, select Data, Sort, press OK and tell Excel to sort everything as text.

3. 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. 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]

5. 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. It sorts correctly when I try it...

7. Would you mind taking a look at my spreadsheet and see if you find my problem?

Thanks

8. 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. 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.

10. 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

11. 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]

12. Hello - Take a look at Sheet 3. Do you get that Warning? If so, choose the second one for your sort.

13. Would you mind helping a newbie who obviously missed something here?

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. Which version of Excel are you using?

- Select your workbook (or a copy of it with personal information removed) and click Open.