View original (French)

IP address sorting

Solved
pheslot Posted messages 61 Registration date   Status Member Last intervention   -  
 pabloEscobar -
Hello,

After importing data into Excel including IP addresses, I can’t sort by these addresses. Here’s what I get:
192.168.101.1
192.168.101.10
192.168.101.101
192.168.101.103
192.168.101.104
192.168.101.105
192.168.101.106
192.168.101.107
192.168.101.108
192.168.101.109
192.168.101.11
192.168.101.111
192.168.101.12
192.168.101.13
192.168.101.139
192.168.101.140
192.168.101.143
192.168.101.145
192.168.101.147
192.168.101.15
192.168.101.17

I tried with the cells in text mode and number mode

Thanks in advance

Configuration: Windows 7 / Firefox 3.6

--
Djeha-Hodja Nasreddin: But you know how difficult it is, my son, to be certain that the one who knows and knows that he knows, really knows.

6 answers

  1.
    pheslot Posted messages 61 Registration date   Status Member Last intervention   5
     
    Wonderful!!!
    A big thank you to you pijaku

    --
    Djeha-Hodja Nasreddin: But, you know how difficult it is, my son, to be certain that the one who knows and knows that he knows truly knows.
    2
  2. pabloEscobar
     
    Hello,

    I’m taking the liberty to bring up the topic again; I had the same problem and I didn’t understand the solution from pijaku (I didn’t really try to understand it either ^^).
    My solution, which also works, is to add "0" to the last byte.

    Best regards
    1
  3. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello

    What do you want to achieve?
    --

    Always zen
    0
  4. pheslot Posted messages 61 Registration date   Status Member Last intervention   5
     
    192.168.101.1
    192.168.101.2
    192.168.101.3
    192.168.101.4
    192.168.101.5
    192.168.101.6
    192.168.101.7
    192.168.101.8
    192.168.101.9
    192.168.101.10
    192.168.101.11

    --
    Djeha-Hodja Nasreddin : Mais, vous savez combien il est difficile, mon fils, d'être certain que celui qui sait et sait qu'il sait, sait vraiment.
    0
  6. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
     
    Hello everyone,
    A simple formula-based solution:
    Assume your IP addresses are in Column A, and all your data in columns B to G. In H1 write:
    =RIGHT(A1;LEN(A1)-FIND(".",A1;10))
    Use the fill handle (the small cross that appears when you hover the mouse over the bottom right corner of the cell) to “extend” this formula down the column.
    Then sort using this column.
    --
    Best regards,
    -- Every problem has a solution. If there is no solution, where is the problem? --
    0
  7. pheslot Posted messages 61 Registration date   Status Member Last intervention   5
     
    Hello pijaku,

    Mea-culpa, it works great, except that I have several networks:

    192.168.101.x
    192.168.102.x

    so when I have:

    192.168.101.10
    192.168.102.10

    they are one after the other.

    Can we adapt?

    --
    Djeha-Hodja Nasreddin: But, you know how difficult it is, my son, to be certain that the one who knows and knows that he knows, truly knows.
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
       
      Re-
      In column A the IP addresses
      in B1: =RIGHT(A1;LEN(A1)-FIND(".";A1;10)) (formula to copy down column B)
      in C1: =MID(A1;9;3) (formula to copy down column C)

      For sorting, choose: Data/Sort
      by column C (ascending)
      then by column B (ascending)
      And there you go
      0