Excel Data Sort problem

Forums › Desktop computing › Excel Data Sort problem

tdgeek

29746 posts

Uber Geek

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

1030 posts

Uber Geek

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

3867 posts

Uber Geek

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

Referral links to services I use, really like, and may be rewarded if you sign up:
PocketSmith for budgeting and personal finance management. A great Kiwi company.

tdgeek

29746 posts

Uber Geek

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

29746 posts

Uber Geek

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

159 posts

Master Geek
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

29746 posts

Uber Geek

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?

Benjip

943 posts

Ultimate Geek

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).

Lenovo

Shop now for Lenovo laptops and other devices (affiliate link).

Deamo

159 posts

Master Geek
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

674 posts

Ultimate Geek

#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

5680 posts

Uber Geek

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

159 posts

Master Geek
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

TwoSeven

1624 posts

Uber Geek

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...