A more efficient formula or macro is highly appreciated

[Thequestion]

Good afternoon,

Every day I need to extract references from large descriptions.
I came up with a formula for the time being to extract some references, however, I'm sure that there are people on this forum who have a much higher Excel skill level than me.

It would be highly appreciated if someone could give me a better formula or VBA-code.

(Unfortunately, my boss changed our English Excel to Dutch.)

Current solution:

Description in column E:

/TRTP/SEPA Overboeking/IBAN/NL92ABNA048/BIC/ABNANL2A/NAME/
WAARBORGFONDS MOTORVERKE/REMI/Dossiernr. 990140071 Onze referent


ie 1388412 Uw referentie 41707053 / 990140071/EREF/ABN636-B0000
144025


[TABLE="width: 605"]
<tbody>[TR]
[TD="class: xl82, width: 605"]/TREF/SEPA CT RECEIVED AFTFR /EREF/NOPROVIDED/ORDP//NAME/VAN ENGELEN INFRA B.V./ID//REMI/2017ZIG0826/ISDT/20170914/ULTD//NAME//ID//ULTB//NAME//ID//PURP//PRTRY//OCMT/EUR408,55/EXCH/1.0000000[/TD]
[/TR]
</tbody>[/TABLE]
My find-mid formula: =ALS(ALS.FOUT(ALS.FOUT(VIND.ALLES("2017U";E4);DEEL(E4;VIND.ALLES("2017U";E4);99));DEEL(E4;VIND.ALLES("2017Z";E4);11))<100;DEEL(E4;VIND.ALLES("2017U";E4);11);DEEL(E4;VIND.ALLES("2017Z";E4);11))

Gives the wanted reference: 2017ZIG0826


Currently, I manually extract a few different types of references with asterisks/stars being random letters and ... being random numbers:

2018***.... (e.g. 2018ZIG0277)
2017***....
B2018***....
B2017***....
2017*****11226 (e.g. 2017AXANL11226)
2017****11226 (e.g. 2017LPNL11226)
B417..... (e.g. 41709651) (always the same length)
417..... (e.g. 41709651) (always the same length)
B61***** (e.g. B6111962) (always the same length)
61***** (e.g. 6111962) (always the same length)
B60***** (e.g. 6011962) (always the same length)
60***** (e.g. 6011962) (always the same length)
BMTR..-NL..... (e.g. BMTR15-NL43842) (always the same length)
MTR..-NL..... (e.g. MTR15-NL43842) (always the same length)
BA.....-..... (e.g. BA12655-10385) (always the same length)
A.....-..... (e.g. A12655-10385) (always the same length)

Currently, I use iferror (als.fout) to notice references which couldn't be extracted automatically