Thequestion
New Member
- Joined
- Jan 27, 2016
- Messages
- 18
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
<tbody>
</tbody>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
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
| /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 |
<tbody>
</tbody>
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
