Excel - Expand IP address range to individual IP ?

[wo1f]

If I've set of data like this in Ms Excel

10.0.0.1-10.0.0.3
172.16.0.3
192.168.0.2-192.168.0.2

How do I expand those IP Address and produce an output like this?

10.0.0.1
10.0.0.2
10.0.0.3
172.16.0.3
192.168.0.2

 

[Eric W]

Welcome to the MrExcel forum!

While a formula could probably be devised to do this, it would be long and complicated. This is more suited to a VBA macro solution. If you want to try that, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. In the sheet that opens, paste this code:

Sub ExpandIP()
Dim ic As Range, oc As Range, arr As Variant, dic As Object, i As Long, j As Long, c As Long
Dim sloc As Long, v1 As String, v2 As String, v1a As String, s1 As Long, s2 As Long

    Set ic = Range("A2")
    Set oc = Range("B1")
    
    arr = Range(ic, ic.End(xlDown)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    dic.Add 0, "Output"
    
    For i = 1 To UBound(arr)
        sloc = InStr(arr(i, 1), "-")
        If sloc > 0 Then
            v1 = Left(arr(i, 1), sloc - 1)
            v2 = Mid(arr(i, 1), sloc + 1)
        Else
            v1 = arr(i, 1)
            v2 = arr(i, 1)
        End If
        v1a = Left(v1, InStrRev(v1, "."))
        s1 = Mid(v1, InStrRev(v1, ".") + 1)
        s2 = Mid(v2, InStrRev(v2, ".") + 1)
        For j = s1 To s2
            c = c + 1
            dic.Add c, v1a & j
        Next j
    Next i
    
    oc.Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.items)
            
End Sub

Change these lines:

    Set ic = Range("A2")
    Set oc = Range("B1")

to point to the input cell and output cell. Now press Alt-Q to close the editor. When you're on the sheet with your data, press Alt-F8. Select ExpandIP and click run. That should do it.

 

[sandy666]

another approach with Power Query

raw		Result
10.0.0.1-10.0.0.3		10.0.0.1
172.16.0.3		10.0.0.2
192.168.0.2-192.168.0.2		10.0.0.3
192.168.0.5-192.168.0.9		172.16.0.3
10.0.0.1		192.168.0.2
		192.168.0.5
		192.168.0.6
		192.168.0.7
		192.168.0.8
		192.168.0.9
		10.0.0.1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "raw", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"raw.1", "raw.2"}),
    TAD1 = Table.AddColumn(Split, "TAD", each Text.AfterDelimiter([raw.1], ".", {0, RelativePosition.FromEnd}), type text),
    TAD2 = Table.AddColumn(TAD1, "TAD1", each if Text.AfterDelimiter([raw.2], ".", {0, RelativePosition.FromEnd}) = "" then [TAD] else Text.AfterDelimiter([raw.2], ".", {0, RelativePosition.FromEnd}), type text),
    TNumber = Table.TransformColumnTypes(TAD2,{{"TAD", Int64.Type}, {"TAD1", Int64.Type}}),
    List = Table.AddColumn(TNumber, "Custom", each {[TAD]..[TAD1]}),
    Expand = Table.ExpandListColumn(List, "Custom"),
    ETBD = Table.TransformColumns(Expand, {{"raw.1", each Text.BeforeDelimiter(_, ".", {0, RelativePosition.FromEnd}), type text}}),
    TSC = Table.SelectColumns(ETBD,{"raw.1", "Custom"}),
    TCC = Table.CombineColumns(Table.TransformColumnTypes(TSC, {{"Custom", type text}}, "en-GB"),{"raw.1", "Custom"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Result")
in
    TCC

 

[Sriram1624]

Do u have any sample excel above one format excel