Rob Covey
unread,
Jul 5, 2001, 12:42:25 AM7/5/01
to
I have been pulling my hair out trying to sort a list of ip
addresses in ascending numerical order. The following is
what happens when I try to sort it.
Eg.
1.1.1.1
1.1.1.251
1.1.1.3
1.1.1.5
1.1.1.61
The result that I am hoping for is;
1.1.1.1
1.1.1.3
1.1.1.5
1.1.1.61
1.1.1.251
Any help would be appreciated
Thanks and regards
Rob
Bill Manville
unread,
Jul 5, 2001, 3:02:27 AM7/5/01
to
In article , Rob Covey wrote:
> The result
that I am hoping for is;
>
> 1.1.1.1
> 1.1.1.3
> 1.1.1.5
> 1.1.1.61
> 1.1.1.251
>
I guess you have to create a formula in a parallel column that gives a result
that will sort correctly,
e.g. giving [[[1*1000]+1]*1000]+1]*1000+251 = 1001001251
Now for the tricky bit - making the formula.
Whilst it would be possible to do it using what John Walkenbach calls a
megaformula, I think it would be easier to use a VBA function.
Function
SortIPCode[IPCode As String] As Double
Dim iDot As Integer
Dim stLeft As String
Dim dResult As Double
stLeft = IPCode
iDot = InStr[stLeft, "."]
Do Until iDot = 0
dResult = dResult * 1000 + Int[Left[stLeft, iDot - 1]]
stLeft = Mid[stLeft, iDot + 1]
iDot = InStr[stLeft, "."]
Loop
SortIPCode = dResult * 1000 + Int[stLeft]
End Function
Put this in a standard module.
In your worksheet, if your IP addresses are in A2:A100, and column B is not
used, put this formula in B2
=SortIPCode[A2]
and fill it down to B100
Then sort on column B.
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
Dana DeLouis
unread,
Jul 6, 2001, 3:50:34 AM7/6/01
to
One of the other ways to do this is to use and split
the data based on the period "."
Sort on columns 4,3,2, then 1.
Then delete these extra columns.
--
HTH
Dana DeLouis Windows Me & Office XP
"Rob Covey" wrote in message
news:2d2d01c1050c$e3849d30$9ae62ecf@tkmsftngxa02...
David McRitchie
unread,
Jul 6, 2001, 10:09:20 AM7/6/01
to
unread,
Apr 12, 2016, 7:13:02 PM4/12/16
to
Amplifying Dana DeLouis' solution...The following method works great. You need 6 columns, but can sort on only one.
Let's say A2 contains the IPv4 address in normal non-zero-filled format like "192.168.15.42". In B2 enter the formula:
=split[A2,"."]
which puts the four numeric address terms in columns B2, C2, D2 and E2. Then in F2 enter
=4294967296*B2+65536*C2+256*D2+E2
For example, IP 192.168.15.42 generates an "IP sort" number 824644734762.
Copy the two formulas down their respective columns, call column E "IPsort" or some such and sort ascending on just this column.
- You can sort alphabetically in Google Sheets to help you better organize term data in your document.
- Here's how to sort alphabetically in Google Sheets on desktop or on mobile.
- Visit Business Insider's homepage for more stories.
Google Sheets is Google's answer to Excel, helping you to create a multitude of spreadsheets within the Google Office Suite.
When making a spreadsheet, it's important to be able to sort your data in different ways, depending on context, in order to help you make sense of it. For example, if you're making a spreadsheet to keep track of donations, sorting from highest to lowest or lowest to highest might be helpful.
Spreadsheets aren't all about numbers, though. What if you want to keep track of something else, like people?
In that case, it would be more helpful, perhaps, to sort alphabetically. Keeping track of people by first or last name from A to Z is one of the most common and familiar means of organization to most people — so if you're doing something like making a guest list or trying to keep track of preferences for a gift exchange, you'll probably want to sort out the names that way.
Here's how to sort alphabetically in Google Sheets, on desktop and mobile.
Check out the products mentioned in this article:
Lenovo IdeaPad 130 [From $299.99 at Best Buy]
MacBook Pro [From $1,299.99 at Best Buy]
iPhone 11 [From $699.99 at Best Buy]
Samsung Galaxy S10 [From $899.99 at Best Buy]
How to sort alphabetically in Google Sheets using your computer
1. Open Google Sheets on your PC or Mac computer. Log in and open the sheet that you want to sort.
2. Click and drag your mouse to highlight the data range you want to sort.
Highlight your desired range. Melanie Weir/Business Insider3. Click the "Data" button in the menu at the top of the screen.
4. In the dropdown menu, click "Sort Range."
5. In the pop-up window, be sure to specify whether or not your columns have headers. If they do, check the box at the top.
- If you want to sort an entire data table with headers, be sure to freeze the first row. Then, at the top of your spreadsheet, click the letter of the column you want to sort by. Right-click the letter, then click "Sort Sheet A > Z."
6. Select the column that you wish to sort alphabetically, then choose either A to Z or Z to A.
7. When you are sure everything is correct, click "Sort."
Choose a column to sort by and how to sort, then click "Sort." Melanie Weir/Business InsiderHow to sort alphabetically in Google Sheets using your mobile device
1. Open the Google Sheets app on your iPhone or Android.
2. Select the spreadsheet that you want to edit.
3. Tap the gray box in the top-left corner of the spreadsheet to select the whole document.
4. Click the three dots in the upper-right corner of your screen.
Select your data, then click the three dots in the top-right corner. Melanie Weir/Business Insider5. In the side menu, tap "Create a filter."
Tap "Create a filter." Melanie Weir/Business Insider6. Tap the green triangle next to the column you want to sort.
Tap the green triangle next to the column title or number. Melanie Weir/Business Insider7. Under "Sort and filter," tap A > Z or Z > A.
Tap "A > Z." Melanie Weir/Business Insider
Related coverage from How To Do Everything: Tech:
How to hide rows in Google Sheets on on a computer or mobile device
How to freeze columns in Google Sheets on desktop or mobile
How to insert multiple rows in Google Sheets in 2 ways, using your PC or Mac
How to multiply in Google Sheets in 3 ways, using a multiplication formula
How to unhide columns and rows in Google Sheets on desktop and mobile
Melanie Weir
Freelance author
Melanie Weir is a freelance author for Insider, mainly focusing on the Tech Reference section, but occasionally contributing to Lifestyle and Entertainment topics as well. She is also a freelance writer for ScreenRant, and is the Lead Weekend News Editor at TheThings.com. In her spare time she writes plays for both stage and screen. She can be reached at , or through LinkedIn.
Read more Read less
Insider Inc. receives a commission when you buy through our links.