Sort with multiple decimal places

Topic

New Reply

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

Reply | Quote

Replies

Select your data, select Data, Sort, press OK and tell Excel to sort everything as text.

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote

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!!

Reply | Quote

It sorts correctly when I try it…

Reply | Quote

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

Thanks

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

Thanks for the responses, guys.

I’m attaching a copy of my spreadsheet.

What’s strange is that the one I created gets the error,
but the FormatSample.xls that I downloaded doesn’t.

Reply | Quote

Hi George,

The problem is that you have given the module the same name as the function: FormatText. This confuses Excel.

You can change the name of the module in the Properties pane on the left hand side of the Visual Basic Editor. If you name it (for example) basFormatText, the function should work correctly when you change anything in the sheet.

Reply | Quote

Sorry, forgot to say I’m using Excel 2003 on an WinXP 3

Reply | Quote

Change the name of your module to something other than FormatText.

Reply | Quote

Hello – My procedure Posted 2009-12-16 13:40 gives the sort (in col D) in the order and format requested in the original question Posted 2009-12-16 11:51.

Also see Posted 2009-12-16 14:21

I use Excel 2003 SP3

Reply | Quote

mdbct and Tim,

It works!

Many thanks for your help.
I figured I had missed some basic tenet.

Never again will I give a function AND a module the same name.

Merry Christmas.

George

Reply | Quote

Hi Tim,
Thanks for your help on this thread. As a side note – you will find that linking to your post instead of quoting the date and time is a more effective way to reference other posts (depending on the viewer’s time zone – the time can be very different than what you see).
To create a link to a post:
Click on the post number link on the right hand side of the post.

Use Ctrl + C to copy the link from the pop-up dialog box

Then in the post that you are writing, paste (Ctrl + V) the URL into the Insert Link dialogue

Once again, thanks for your help and welcome to the Lounge!

Reply | Quote

Hello WebGenii – Thank you very much for showing me how to post a quote from a prior post. I am new to this site and I very much appreciate your instructions.

Thanks again,
Tim

Reply | Quote