View original (French)

IP address sorting

Solved
pheslot -  
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 Author
    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
    1. gbinforme Contributor
      Hello

      What do you want to achieve?
      --

      Always zen
      0
      1. pheslot Author
        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
        1. pijaku Moderator
          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
          2. pheslot Author
            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 Moderator
              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
          3. 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