Sort a list of IP Addresses in Excel 3

[KellyStee]

Anyone have a great idea of how to sort a list of IP addresses? Excel sorts my list like this:
1.10.1.5
1.100.1.5
1.1000.1.5
1.2.1.5
1.200.10.5
1.3.1.5

I need it sorted like this:
1.2.1.5
1.3.1.5
1.10.1.15
1.100.1.5
1.200.10.5
1.1000.1.5

One good way to make this work would be to break each ip address into four distinct columns and then sort A,B,C & then D, but I can't think of any formuals that would divide the cell based on when a decimal is encountered.

 

[DaleWatson123321]

Hi Kelly,

I have created a sample file, which I am prepared to email you if you have any difficulty with the formula.

With the following formula, you would enter it into the column next to your IP addresses, and then sort BOTH columns, using the formula column as the sort column.

=VALUE(MID(A1,FIND(".",A1,1)+1,FIND(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1))

This formula is created based on the following:

1) The "sort portion" of the number will always be the second portion - i.e. between the 1st and 2nd periods.

2) The formula allows for the 1st portion (which is currently 1 digit) to be more than 1 digit. I have to admit I don't know enought about IP addresses to know whether this 1st portion will ever exceed 1 digit, but nonetheless, the formula will still work if the number exceeds the 1 digit.

I expect this will work for you. Please advise if you have any difficulty, and don't forget my offer of emailing the file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[vbMax]

Dale's solution works except, it will only sort
by the second group of numbers. I built on Dale's
logic to come up with the following solution which
will sort by all four groups of numbers.

Assuming the IP addresses are in column A...
place the following formula in column B:
=FIND(".",A1,1)

place the following formula in column C:
=FIND(".",A1,FIND(".",A1,1)+1)

place the following formula in column D:
=FIND(".", A1, FIND(".",A1,FIND(".",A1,1)+1)+1)

place the following formula in column E:
= MID("000", 1, 4-B1) & MID(A1, 1, B1 - 1)
& "." & MID("000", 1, 4 - (C1-B1)) &
MID(A1, B1 + 1, C1-B1-1) & "." &
MID("000", 1, 4 - (D1-C1)) &
MID(A1, C1 + 1, D1-C1-1) & "." &
MID("000", 1, 3 - (LEN(A1)-D1)) &
MID(A1, D1 + 1, LEN(A1)-D1)

Then, select columns A:E and sort by column
E. I know this formula is complex, but it
does work!

X-)

 

[hondurab]

How might I go about counting the instances of a particular string in a memo field?

For example, in a work log field, I want to count the number of entries for "John Doe".

Please give me details - thank you!

 

[psudeke]

How about something much simpler. Select the IP's, go to data, text to columns, set your delimeter to ".", that will place the values in the next three columns parsed correctly. Good Luck.

 

[JVFriederick]

Excellent suggestion.

 

[KellyStee]

Absolutely simple and brilliant!!! Thanks!!

 

[DaleWatson123321]

Hi psudeke,

Thanks also for your simple solution. Simple is definitely better.

...Dale Watson