sorting IP addresses

[tmt7734]

Not sure where to put this question, no flames please.

I'm trying to sort data by the list of ip addresses in an excel 2000 spreadsheet. Excel sorts it like .1, .10, .100, .11, etc.. Anyone kno how format the cells correctly for IP addresses in Excel 2000?

Thanks

 

[Cagliostro]

I think you should put this question in the excel forum.

Ion Filipski

 

[tmt7734]

What excel forum. I don't see one, am I blind?

 

[John Lewis]

I would agree that you probably should be in an Excel/Office forum, but in the spirit of helping . . .

You need to get that last octet in a separate cell and then sort by that cell. The cell needs to have it's format set to number for the sorting to work properly. A workable formula to pull the last octet off would be:

=MID(A1,(FIND(".",A1,((FIND(".",A1,((FIND(".",A1,1)+1)))+1)))+1),3)

The formula assumes that your IP is in cell A1, so change the A1's to match where yours is at. Also, you could hide the column if you do not want it to show on your sheet. That will allow for different sized first, second and third octets. If those will be constant, you could simply use:

=MID(A1,12,3)

adjusting the 12 to the length you have.

Both of those should work, but you may want to browse over to the Excel and/or Office forum, the people over there might come up with something a bit neater.

 

[John Lewis]

Went to look, and I guess there is no Excel forum. Office should do, forum68