Excel Data Sort problem

Forums › Desktop computing › Excel Data Sort problem

tdgeek

30048 posts

Uber Geek
+1 received by user: 9455

Trusted

Lifetime subscriber

#279979 20-Nov-2020 09:04

I need to sort thousands of IP addresses. xxx.xxx.xxx.xxx for audit purposes

But the issue is they stay in this order re the last 3 digits as below. I want them in strict numerical order 1 2 3 4 5 6 7 8 9 10 11 12 and so on

Ive tried all manner of options in Excel, including 000.000.000.00 custom format to no avail

Any ideas? Its probably easy but I can't find it

100

101

102

103

104

105

106

107

108

109

110

kiwiharry

1064 posts

Uber Geek
+1 received by user: 476

ID Verified

Subscriber

#2607316 20-Nov-2020 09:14

Maybe this may help you.

https://excelribbon.tips.net/T013481_Sorting_IP_Addresses.html

If you can't laugh at yourself then you probably shouldn't laugh at others.

Dynamic

4015 posts

Uber Geek
+1 received by user: 1851

ID Verified

Trusted

Lifetime subscriber

#2607317 20-Nov-2020 09:14

Does it help if you tell Excel to treat that column as text?

“Don't believe anything you read on the net. Except this. Well, including this, I suppose.” Douglas Adams

tdgeek

30048 posts

Uber Geek
+1 received by user: 9455

Trusted

Lifetime subscriber

#2607321 20-Nov-2020 09:21

Dynamic:

Does it help if you tell Excel to treat that column as text?

Nope, ive tried literally all formats apart from date time etc

tdgeek

30048 posts

Uber Geek
+1 received by user: 9455

Trusted

Lifetime subscriber

#2607324 20-Nov-2020 09:23

kiwiharry:

Maybe this may help you.

https://excelribbon.tips.net/T013481_Sorting_IP_Addresses.html

Awesome thanks, I'll have a look at that later today :-)

Deamo

144 posts

Master Geek
+1 received by user: 63
Inactive user

#2607327 20-Nov-2020 09:27

Set up a helper column that strips the . from the IP address and sort on that column instead - that way it'll sort in numeric order

tdgeek

30048 posts

Uber Geek
+1 received by user: 9455

Trusted

Lifetime subscriber

#2607336 20-Nov-2020 09:35

Deamo:

Set up a helper column that strips the . from the IP address and sort on that column instead - that way it'll sort in numeric order

Thats sounds a more simple solution, how do I do that?

Geekzone

Want to support Geekzone and browse the site without the ads? Subscribe to Geekzone now (monthly, annual and lifetime options).

Benjip

977 posts

Ultimate Geek
+1 received by user: 524

ID Verified

#2607337 20-Nov-2020 09:36

When I have issues like this, I often just copy & paste into Sublime Text (I'm sure other plain text editors could do it too) and do the sorting from there.

Excel really struggles with having zeroes at the front of numbers, in my experience (don't even get me started on NZ phone numbers eg. 021).

Deamo

144 posts

Master Geek
+1 received by user: 63
Inactive user

#2607365 20-Nov-2020 10:07

tdgeek:

Deamo:

Set up a helper column that strips the . from the IP address and sort on that column instead - that way it'll sort in numeric order

Thats sounds a more simple solution, how do I do that?

In a new column use this formula:

=int(substitute([ip address column],".",""))

Then sort as normal on this new column

outdoorsnz

694 posts

Ultimate Geek
+1 received by user: 303

ID Verified

#2607408 20-Nov-2020 10:08

I just had a quick test with this. Found the function split on a delimiter worked well for this.

=split(COLUMN,".") which split the IP address into four columns. You could then define your sorting logic on the four columns...

frankv

5705 posts

Uber Geek
+1 received by user: 3666

Lifetime subscriber

#2607417 20-Nov-2020 10:42

Deamo:

In a new column use this formula:

=int(substitute([ip address column],".",""))

Then sort as normal on this new column

No!!!! That will sort completely wrongly. e.g. 1.101.1.1 and 1.10.11.1 as the same thing.

I would add 7 new columns. Assuming A1 contains your first IP address, put the following into B1 and C1

=INT(LEFT(A1,FIND(".",A1)))

=MID(A1,FIND(".",A1)+1,11)

Then copy B1:C1 to D1:E1 and again to F1:G1

Change G1 by putting it into an INT() i.e. =INT(MID(E1,FIND(".",E1)+1,11))

Copy columns B:G down the length of your IP address list

Hide columns C & E

Sort on columns B, D, F, G

[edit: Overlapped with the above. split() is better if you have it. My version of Excel doesn't]

Deamo

144 posts

Master Geek
+1 received by user: 63
Inactive user

#2607426 20-Nov-2020 10:53

frankv:

No!!!! That will sort completely wrongly. e.g. 1.101.1.1 and 1.10.11.1 as the same thing.

Yes, to keep related IP addresse together, splitting into other columns is the only option.

For a strict numerical sort, as the op stated, int & substitute are sufficient

Samsung

Shop now on Samsung phones, tablets, TVs and more (affiliate link).

TwoSeven

1712 posts

Uber Geek
+1 received by user: 304

Subscriber

#2607932 21-Nov-2020 11:07

Not sure if this may be helpful but what I do is use the data menu in Excel.

Basically on the data menu one imports the data from a source to create a ‘data table’, then one creates a ‘query’ from the data.query menu to slice and dice the data.

For example, with an ip address one might use ‘split by delimiter’ to create some additional columns, then sort by column (primary, secondary) to sort the columns.

Software Engineer
(the practice of real science, engineering and management)
A.I. (Automation rebranded)
Gender Neutral
(a person who believes in equality and who does not believe in/use stereotypes. Examples such as gender, binary, nonbinary, male/female etc.)

...they/their/them...