?
Solved

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

Posted on 2015-02-03
5
Medium Priority
?
482 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 2000 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:Naresh Patel
ID: 40587179
Attached Sample WB please.

Thanks
0
 
LVL 70

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 5

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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