java正则替换excel公式_Excel正则替换文本内容

犯了个失误，在excel中下拉数据时不小心端口号自增了。大概上万条数据，写了个正则表达式，完美解决问题。

curl -X GET http://10.0.0.150:8090/xxx?param=3000001;

curl -X GET http://10.0.0.150:8090/xxx?param=3000001;

curl -X GET http://10.0.0.150:8091/xxx?param=3000001;

Private Sub RegExp_Replace()

Dim RegExp As Object

Dim SearchRange As Range, Cell As Range

'此处定义正则表达式

Set RegExp = CreateObject("vbscript.regexp")

RegExp.Pattern = "150:.*/"

'此处指定查找范围

Set SearchRange = ActiveSheet.Range("A1:A5000")

'遍历查找范围内的单元格

For Each Cell In SearchRange

Set Matches = RegExp.Execute(Cell.Value)

If Matches.Count >= 1 Then

Set Match = Matches(0)

Cell.Value = RegExp.Replace(Cell.Value, "150:8090/")

End If

Next

End Sub