Option Explicit
Public Function MyReplaceFunc(Expr As Variant) As Variant
With CreateObject("VBScript.RegExp")
.Global = False
.MultiLine = False
.IgnoreCase = True
.Pattern = "([a-z]+)0*(\d+)"
MyReplaceFunc = .Replace(Expr, "$1($2)")
End With
End Function
Sub Test()
Dim cell As Excel.Range
With Worksheets("Tabelle1").Range("A1:A4")
.Value = [{"A01";"TG01";"PP0099";"WE020";"P020"}]
For Each cell In .Cells
cell.Offset(0, 1).Value = MyReplaceFunc(cell.Value)
Next
End With
End Sub