Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

Need to sort switch port numbers in numerical order in Excel 2010

I have a spreadsheet where I am sorting all of my core switch ports so we can identify their connections. I have filtering on the column headers so I can sort by different data. It seems that Excel keeps sorting the numbers in a weird way:

1
10
11
2
20
21

I need it to look like this:

1
2
3
4
5
6

I know it's something easy but the sort/filter options don't appear to give an obvious solution. I have formatted the column to be numbers and not text, but there is text in the cells. See below.
Excel sorting numbers in an undesired way.
What can I do to get the numbers sorted in sequential order?
0
Paul Wagner
Asked:
Paul Wagner
1 Solution
 
Katie PierceCommented:
Excel is sorting by the first digit it comes across, thus all the 1s, then all the 2s, etc.  Can you put a 0 in front of the single digit labels (e.g. 01)?
0
 
Katie PierceCommented:
Otherwise you can do "Text to Columns", parsing out the first 15 characters of the cell, leaving the number itself, which Excel can then sort on.
0
 
Naresh PatelTraderCommented:
Attached Sample WB please.

Thanks
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Definitely use two columns - a switch one, and ports in another.
If you don't like to do that, prefix single-digit entries with a 0 or a space.

BTW, the column title is reversed, it's switch / port.
0
 
Paul WagnerFriend To Robots and RocksAuthor Commented:
Adding a zero in front of the single digit numbers worked and did exactly what I needed. They are all sorted in order now. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now