跳到主要內容

如何在Excel中快速將IP地址從低到高排序?

通常,我們在Excel中使用Sort函數對字符串進行排序。 但是,如果需要一些IP地址進行排序,則直接使用“排序”功能可能會導致排序順序錯誤,如下面的屏幕截圖所示。 現在,我有一些方法可以在Excel中快速正確地對IP地址進行排序。

排序功能排序錯誤 正確排序
doc排序ip 1 doc排序ip 2

按公式對IP地址進行排序

按VBA排序IP地址

按文本到列對IP地址排序


按公式對IP地址進行排序

使用公式填充IP地址,然後排序。

1.選擇一個與IP地址相鄰的單元格,然後鍵入此公式

=TEXT(LEFT(A1,FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND( ".",A1,1)+1,FIND(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND(".",A1,FIND(".",A1,1)+1)+1,FIND(".",A1, FIND(".",A1,FIND(".",A1,1)+1)+1)-FIND(".",A1,FIND(".",A1,1)+1)-1), "000") & "." & TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND( ".",A1,1)+1)+1)),"000")

Enter 鍵,然後將填充手柄拖動到單元格上以應用此公式。
doc排序ip 3

2.複製公式結果並將其作為值粘貼在下一列中。 看截圖:

doc排序ip 4
doc排序ip 5

3.保持粘貼值處於選中狀態,然後單擊 數據 > 將A到Z排序.
doc排序ip 6

4。 在裡面 排序警告 對話,保持 擴大選擇 檢查。
doc排序ip 7

5。 單擊 分類。 現在,IP地址已從低到高排序。
doc排序ip 2

您可以刪除幫助器列。


按VBA排序IP地址

這是一個VBA代碼,也可以為您提供幫助。

1。 按 Alt + F11鍵 啟用 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,將代碼複製並粘貼到空白腳本中。

VBA:填寫IP地址

Sub FormatIP()
'UpdatbyExtendoffice20171215
    Dim xReg As New RegExp
    Dim xMatches As MatchCollection
    Dim xMatch As Match
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim xArr() As String
    On Error Resume Next
    Set xRg = Application.InputBox("Select cells:", "KuTools For Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    With xReg
        .Global = True
        .Pattern = "\d{1,3}\.+\d{1,3}\.+\d{1,3}\.+\d{1,3}"
        For Each xCell In xRg
            Set xMatches = .Execute(xCell.Value)
            If xMatches.Count = 0 Then GoTo xBreak
            For Each xMatch In xMatches
                xArr = Split(xMatch, ".")
                For I = 0 To UBound(xArr)
                    xArr(I) = Right("000" & xArr(I), 3)
                    If I <> UBound(xArr) Then
                        xArr(I) = xArr(I) & "."
                    End If
                Next
            Next
            xCell.Value = Join(xArr, "")
xBreak:
        Next
    End With
End Sub

doc排序ip 8

3。 然後點擊 工具 > 參數支持,並檢查 Microsoft VBScript正則表達式5.5 在彈出的對話框中。

doc排序ip 9
doc排序ip 10

4。 點擊 OK 並按下 F5 鍵,會跳出對話方塊提醒您選擇要工作的範圍。
doc排序ip 11

5。 點擊 OK。 然後IP位址就被補零了。

6.選擇IP地址,然後單擊 數據 > 將A到Z排序 對它們進行排序。


按文本到列對IP地址排序

實際上,Excel 中的「文字分列」功能也可以為您提供協助。

1.選擇您使用的單元格,然後單擊 數據 > 文本到列。 看截圖:
doc排序ip 12

2。 在裡面 將文本轉換為列嚮導 對話框,請執行以下操作:

格紋 分隔,然後點擊 下一頁;

格紋 其他 和類型 . 進入文字框,然後按一下 下一頁;

選擇 IP 位址旁的儲存格來放置結果。 點選 .

doc排序ip 13
doc排序ip 14
doc排序ip 15

3. 選取包含 IP 位址和分割儲存格的所有儲存格,然後按一下 數據 > 分類.
doc排序ip 16

4。 在裡面 分類 對話框,點選 添加等級 將 B 列到 E 列(拆分單元格)的資料進行排序。 看截圖:
doc排序ip 17

5。 點擊 OK。 現在列已排序。
doc排序ip 18


最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Traducido al español:
=TEXTO(IZQUIERDA(A1,ENCONTRAR(".",A1,1)-1),"000") & "." & TEXTO(EXTRAE(A1,ENCONTRAR( ".",A1,1)+1,ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)-ENCONTRAR(".",A1,1)-1),"000") & "." & TEXTO(EXTRAE(A1,ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)+1,ENCONTRAR(".",A1, ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)+1)-ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)-1), "000") & "." & TEXTO(DERECHA(A1,LARGO(A1)-ENCONTRAR(".",A1,ENCONTRAR(".",A1,ENCONTRAR( ".",A1,1)+1)+1)),"000")
This comment was minimized by the moderator on the site
Thanks for your help on translating the formula.
This comment was minimized by the moderator on the site
This is great! Very much appreciated. It would be nice to add to the VB code to reverse (put back) the IP's in the original format (without the extra leading 0's) after having sorted them. :) I would be very interested in that where you can run this script, sort results, then revert back to original format.
This comment was minimized by the moderator on the site
thank you, very useful !
This comment was minimized by the moderator on the site
la formule traduite en Francais :
=TEXTE(GAUCHE(I6;TROUVE(".";I6;1)-1);"000") & "." & TEXTE(STXT(I6;TROUVE( ".";I6;1)+1;TROUVE(".";I6;TROUVE(".";I6;1)+1)-TROUVE(".";I6;1)-1);"000") & "." & TEXTE(STXT(I6;TROUVE(".";I6;TROUVE(".";I6;1)+1)+1;TROUVE(".";I6; TROUVE(".";I6;TROUVE(".";I6;1)+1)+1)-TROUVE(".";I6;TROUVE(".";I6;1)+1)-1); "000") & "." & TEXTE(DROITE(I6;NBCAR(I6)-TROUVE(".";I6;TROUVE(".";I6;TROUVE( ".";I6;1)+1)+1));"000")
This comment was minimized by the moderator on the site
Merci pour la traduction !
This comment was minimized by the moderator on the site
Why does the formula not work for the last octet? It adds zeros to the 3rd octet but not the last? So frustrating.
This comment was minimized by the moderator on the site
I have tested the formula before I post it, it can work for the last octer. Have you checked the formula you pasted is correct?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations