Solved

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

Posted on 2015-02-03
5
381 Views
Last Modified: 2015-02-04
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
Comment
Question by:Paul Wagner
5 Comments
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 500 total points
ID: 40587173
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
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40587177
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
 
LVL 8

Expert Comment

by:itjockey
ID: 40587179
Attached Sample WB please.

Thanks
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40587205
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
 
LVL 4

Author Closing Comment

by:Paul Wagner
ID: 40589272
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question