Excel Numbering sequence issue

Question

I am trying to number a spreadsheet with the following format (1.0, 1.1, 1.2, 1.3). However when I do a sort by it jumps my numbers around. For example if I use 1.0, 1.1, 1.2, 1.3 etc. when I sort it places the 1.10 under 1.0. How do I stop that from happening without changing the number format to 1.00, 1.01, 1.02, etc.&nbsp; Thank you for any assistance! I greatly appreciate you!

soraiya · Answer

mtarler&nbsp;&nbsp;Thank you! That did it!&nbsp; Woohoo!&nbsp; I appreciate all your help! Thank goodness I can now finish my spreadsheets!&nbsp;

mtarler · Answer

That is because you have those "numbers" inserted as text and excel is sorting them as text
I have 2 suggestions for you:
a) create a helper column that actually uses the numbers and use that for sorting
b) use a space instead of a "0" so then 1.1 looks like 1. 1 which is pretty good (IMHO) and will sort the way you want (tip: don't forget to add a space before the first digit so when you get to 10.x it will sort correctly)

soraiya · Answer

Hi mtarler,Thank you! Unfortunately, it is still placing 1.10 under 1. 0. 😞

mtarler · Answer

ok maybe your space is different than mine. lol.by any chance is your 1.10 entered as a number while the 1. 0 is text?try entering '1.10 that ' will force it to be text.

sergeibaklan · Answer

Soraiya&nbsp;
If texts with spaces as&nbsp;mtarler&nbsp;suggested when it shall be the prompt

Selecting bottom option

Forum Discussion

Excel Numbering sequence issue

6 Replies

Resources