Excel sort

Excel Excel 2007
February 12, 2010 at 12:52:14
Specs: Windows XP
The devices in our business use static IP adresses. I am making a port map. When I sort it does so to the fourth number as ( you would expect but I believe the program is looking at the fourth number as a decimial. It sorts 1, 101, 102, 11, 112, 113 etc. The hundreds need to be at the end of the list. How do I fix this?

See More: Excel sort

Report •

February 12, 2010 at 13:53:00

How about splitting your IP addresses up first
Select the column of IP addresses (have three columns empty to the right), from the Ribbon - Data - Data Tools -Text to Columns, follow prompts using "." as a delimiter
Label the four columns A, B, C & D
Select all four columns with headers plus adjacent columns with data to move with the IP addresses
Ribbon - Data - Sort & Filter - Sort and select C for the first sort and D for the second level sort
(I guess you won't need to sort on the first two groups for a private IP address range)

Then in the fifth column use a concatenation to pull them back together

BTW I think that the sort order is based on a character sort algorithm, as the IP addresses will be treated as text.


Report •
Related Solutions

Ask Question