Sort IP addresses

PerfectTommy · Oct 4, 2002

I need to sort a range of IP addresses. The problem I am having is this:

xxx.xxx.xxx.1
xxx.xxx.xxx.2
xxx.xxx.xxx.11

sorts as

xxx.xxx.xxx.1
xxx.xxx.xxx.11
xxx.xxx.xxx.2

how can I fix this?

I appreciate any help. Thank you.

Tommy

Von Pookie · Oct 4, 2002

Here ya go.

I knew I'd seen this question before, just had to dig it up

PerfectTommy · Oct 4, 2002

Thanks for the reply, Von Pookie.

I actually looked at that topic before I posted, but according to Mark W. (if I am reading this correctly) that solution actually does not work.

Any other thoughts/suggestions?

Thanks

paliman · Oct 4, 2002

If your list is in column A, type this formula in B1 and copy down:

=VALUE(RIGHT(A1,LEN(A1)-12))

and then sort on column B

does it help?

(the "12" at the end is the number of characters before the changing part of the address: "xxx.xxx.xxx." you may have to adjust it to your needs)

Von Pookie · Oct 4, 2002

I've never actually tried that myself, but Mark also links to another post on the same topic. As far as I understood, he was just saying that the formula that Aladin posted there wouldn't work.

Other than pointing you in that direction, I personally can't really help, sorry.

_________________<font color="#3399FF" size="3">Kristy</font>

This message was edited by Von Pookie on 2002-10-04 13:16

Ian Mac · Oct 4, 2002

try:

=RIGHT(A1,4-FIND(".",RIGHT(A1,4)))+0

where A1 is the first number.
Then sort on that.

Ian Mac · Oct 4, 2002

Also,

what happens in this case:

Book1

A

B

C

D

1

155.113.206.6

6

2

94.205.48.6

6

3

135.186.99.10

10

4

128.248.247.12

12

5

172.205.61.16

16

6

236.53.229.17

17

7

59.147.49.20

20

8

156.127.39.27

27

9

93.129.157.31

31

10

59.7.230.44

44

11

188.251.24.44

44

12

253.237.87.45

45

13

21.84.97.54

54

14

90.240.159.54

54

15

254.175.217.55

55

16

183.58.166.65

65

17

187.15.98.67

67

18

178.236.176.71

71

19

252.166.18.73

73

20

149.61.161.74

74

Sheet1

PerfectTommy · Oct 4, 2002

Ian Mac,

Thank you. Your formula works perfectly for what I asked for. Unfortunately, (and I think you guessed this from your second post) I did not ask for the right thing...

The IP addresses I need to sort actually have this pattern:

xxx.xxx.yyy.zzz

Do you perhaps have something that will sort the addresses according to the "yyy" set, and then the "zzz" set?

I apologize for the mistake.

Thank you (everyone) for your help.
This message was edited by PerfectTommy on 2002-10-04 13:26

Ekim · Oct 4, 2002

1. Your IP addresses starting in A1

2. In B1 =LEFT(A10,11) copy down (to produce xxx.xxx.xxx)

3. In C1 uses Ian's formula (copy down).

4. Sort on Column C.

5. For columns B and C, copy/paste special/values to strip away the formulas.

6. In D1 = B1&"."&C1 copy down.

Regards,

Mike