Solved

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

Posted on 2015-02-03
5
451 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 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: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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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