我在 Excel 中有很长的 IP 列表，这些 IP 格式对于某个站点无效。我必须手动更改/重新键入每个文件才能将文件上传到站点。
如何为以下示例创建自定义公式、vba 或格式代码？
左边的IP格式需要是右边的格式。
示例：
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 自动填充/快速填充未激活。换句话说，Excel 不会为我挑选模式或填写其余部分。
我制作了一个函数，可以将字符串输入转换为您要求的格式。
它使用Split函数从起始和结束范围比较ip地址的每一部分。
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
以下是如何使用该功能的示例
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