Excel Numbering sequence issue

%3CLINGO-SUB%20id%3D%22lingo-sub-3214292%22%20slang%3D%22en-US%22%3EExcel%20Numbering%20sequence%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3214292%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20number%20a%20spreadsheet%20with%20the%20following%20format%20(1.0%2C%201.1%2C%201.2%2C%201.3).%20However%20when%20I%20do%20a%20sort%20by%20it%20jumps%20my%20numbers%20around.%20For%20example%20if%20I%20use%201.0%2C%201.1%2C%201.2%2C%201.3%20etc.%20when%20I%20sort%20it%20places%20the%201.10%20under%201.0.%20How%20do%20I%20stop%20that%20from%20happening%20without%20changing%20the%20number%20format%20to%201.00%2C%201.01%2C%201.02%2C%20etc.%26nbsp%3B%20Thank%20you%20for%20any%20assistance!%20I%20greatly%20appreciate%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3214292%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3214502%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Numbering%20sequence%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3214502%22%20slang%3D%22en-US%22%3EHi%20mtarler%2C%3CBR%20%2F%3EThank%20you!%20Unfortunately%2C%20it%20is%20still%20placing%201.10%20under%201.%200.%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3214328%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Numbering%20sequence%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3214328%22%20slang%3D%22en-US%22%3EThat%20is%20because%20you%20have%20those%20%22numbers%22%20inserted%20as%20text%20and%20excel%20is%20sorting%20them%20as%20text%3CBR%20%2F%3EI%20have%202%20suggestions%20for%20you%3A%3CBR%20%2F%3Ea)%20create%20a%20helper%20column%20that%20actually%20uses%20the%20numbers%20and%20use%20that%20for%20sorting%3CBR%20%2F%3Eb)%20use%20a%20space%20instead%20of%20a%20%220%22%20so%20then%201.1%20looks%20like%201.%201%20which%20is%20pretty%20good%20(IMHO)%20and%20will%20sort%20the%20way%20you%20want%20(tip%3A%20don't%20forget%20to%20add%20a%20space%20before%20the%20first%20digit%20so%20when%20you%20get%20to%2010.x%20it%20will%20sort%20correctly)%3C%2FLINGO-BODY%3E
New Contributor

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.  Thank you for any assistance! I greatly appreciate you!

6 Replies
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)
Hi mtarler,
Thank you! Unfortunately, it is still placing 1.10 under 1. 0.
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.

@Soraiya 

If texts with spaces as @mtarler suggested when it shall be the prompt

image.png

Selecting bottom option

image.png

@mtarler 

 

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

Thank you Sergei! It is finally acting like I wanted it too. I appreciate your assistance. Between you and @mtarler i was able to get it formatted correctly.