Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • 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
 
QlemoC++ DeveloperCommented:
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

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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