Option Explicit
Sub TestRegEx()
Dim c As Range, Arr() As Variant
With Columns("A")
For Each c In .Range(.Cells(1), .Cells(.Cells.Count).End(xlUp))
On Error Resume Next
Arr = SplitAt(c)
If Err.Number = 0 Then c.Offset(, 1).Resize(1, UBound(Arr)).Value = Arr
On Error GoTo 0
Next c
End With
End Sub
Function SplitAt(myCell As Range) As Variant
Dim oRegEx As New RegExp
Dim strPattern As String
Dim strValue As String
Dim objPosition As Object
Dim Hit, arrHit(1 To 2)
strPattern = "[0-9]{5}"
strValue = myCell.Value
With oRegEx
.Global = True
.MultiLine = False
.Pattern = strPattern
If .Test(strValue) Then
Set objPosition = oRegEx.Execute(strValue)
Hit = objPosition(0).FirstIndex
arrHit(1) = Left(strValue, Hit - 1)
arrHit(2) = Mid(strValue, Hit + 1)
SplitAt = arrHit
End If
End With
End Function