How do I create a custom format, formula or VBA code for the below examples?

Question

I have a long list of IPs in Excel that are in an invalid format for a certain site. I have to change/retype each one manually in order to upload the file to the site.

How do I create a custom formula, vba, or format code for the following examples?

The IP format on the left needs to be in the format on the right.

Examples:

123.45.678.0 - 123.45.679.255   should be -->   123.45.678-679.*
123.45.678.9 - 123.45.678.12  should be -->   123.45.678.9-12
a.b.c.d - a.b.e.f   should be -->   a.b.c-e.*       
a.b.c.d - a.b.c.f   should be -->   a.b.c.d-f

QuickAnalysis Autofill/Flashfill is not activating when I complete a few manually. In other words, Excel isn't picking up the pattern or filling in the rest for me.

Answer 1

I made a function that converts string inputs into your requested format.

It uses the Split function to compare each part of the ip address from the starting and ending range.

Function ipFormatter(ByVal ipRange As String) As String
    'Skip inputs with no "-"
    If InStr(1, ipRange, "-") = 0 Then
        ipFormatter = ipRange
        Exit Function
    End If
    
    'Removing all spaces
    ipRange = Replace(ipRange, " ", "")
    
    'Splitting the ip addresses into arrays of strings
    Dim ipStart() As String, ipEnd() As String
    ipStart = Split(Split(ipRange, "-")(0), ".")
    ipEnd = Split(Split(ipRange, "-")(1), ".")
    
    Dim ipOut() As String
    ReDim ipOut(UBound(ipStart))
    
    Dim i As Long, setRange As Boolean
    For i = LBound(ipStart) To UBound(ipStart)
        If setRange Then
            'After a range
            ipOut(i) = "*"
        ElseIf ipStart(i) = ipEnd(i) Then
            'Exact Match
            ipOut(i) = ipStart(i)
        Else
            'IP Range found
            ipOut(i) = ipStart(i) & "-" & ipEnd(i)
            setRange = True 'All values after this one will be "*"
        End If
    Next i
    
    ipFormatter = Join(ipOut, ".")
End Function

Here are examples of how to use the function

Sub test()
    Const inputstr As String = "123.45.678.0 - 123.45.679.255"
    
    MsgBox ipFormatter(inputstr)
End Sub

Sub example()
    Dim Sht As Worksheet
    Set Sht = ThisWorkbook.Sheets("Sheet1")
    
    Dim lastrow As Long
    lastrow = Sht.Rows(Sht.Rows.Count).Cells(1).End(xlUp).Row
    
    Dim i As Long
    For i = 1 To lastrow
        Sht.Cells(i, 2) = ipFormatter(Sht.Cells(i, 1))
    Next i
End Sub