Selected Answer
I was able to come up with this formula.
=MID(MID(A2,FIND(CHAR(40),A2)-6,6),MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),4)
It looks for the first number in MID(A2,FIND(CHAR(40),A2)-6,6), based on the assumption that there will only be 4 numerics in the six characters before the left parenthesis, and then takes 4 characters from that point. It will fail if there are less than 6 characters before "(" or if the 4-digit number you want is part of a larger number.
Your idea to look from the right is better. In Excel 2016 there is a function to reverse a string. My efforts to do it with an array formula failed. Therefore I wrote a UDF (User Defined Function).
Function ExtractID(Cell As Range) As Variant
' Variatus @TeachExcel
' 10 Jan 2020
Dim Fun As Variant
Fun = Cell.Value
Fun = Mid(Fun, InStr(Fun, Chr(40)) - 6, 6)
Do While Len(Fun) And (Not IsNumeric(Right(Fun, 1)))
Fun = Left(Fun, Len(Fun) - 1)
Loop
ExtractID = Right(Fun, 4)
End Function
You will find it deployed in the sheet Solution 2 of the attached workbook. The obvious advantage of a UDF is that you can tweak it for lots of eventualities. The above code is rudimetary.
On the tab Solution 1 of the attached workbook you will find a breakdown of the worksheet function. The key element it introduces to our discussion is the function to look for a number in a string. This I am indebted for to this site where it is further explained. I wanted to deploy it in a reversed string, looking for the first number to the left of "(". Perhaps you can succeed where I failed. Or you might use it to find the number you want from the right, forgetting "(" as a point of reference entirely.
