Sorting Excel by IP address

Select the column with the IP addresses in it and then choose Data>Text to Columns.

The first window should show the data is already Delimited. Click Next.

Set the delimiter to Other and put in a full stop (dot) in the box. Remove any other delimiters if selected. Click Next.

Change Destination to cover four new free columns. For example, enter $R:$U to send IP address octets to. Click Finish.

Select the column with the fourth octet in it and sort on it. This will sort all data based on this index.

Once you have broken down the IP address into octets you can sort the data based on 4th octet or 3rd octet which maybe required based on the network mask. Select a cell in the column you wish to sort and right click the mouse and select Sort.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s