I have a column with each cell containing text with a reference which I need to extract, the reference is always 10 characters with first 2 being letters (either NL,LT or BE) followed by 5 numbers, followed by a "-" and 2 numbers at the end. The reference is not always in the same position in the string. Couple of examples below:
EXAMPLE 1
DIMOVA, ADRIANA
NONREF 4353647465464336
/OCMT/EUR60,71/ORD.BANK/DEUTDEDBMUC/ORD.CUST/DIMOVA, ADRIANA/PAY
.DET./RECHNUNGNR. NL76549-10 BUSINESS-ID 76549"
EXAMPLE 2
CLAUDIA ZINAYIDA
TFR+
BE75491-25 BEAUTIQUE CRISTINA FERNAN DES
CHARGE WAIVED
I'm looking for a formula which will pick up the string "NL76549-10" in example 1 and "BE75491-25" in example 2.
I've tried a formula along the lines of
IF(FIND("NL",A1,20)-FIND("-",A1,FIND("NL",A1,20))=-7,MID(A1,FIND("NL",A1,20),10),"CHECK")
and combining the iferror function search for BE and LT however I run into issues when there are multiple instances of "NL" or "BE" or "LT" in the string and the reference number is not always the first or the last to contain the string.
Any help will be greatly appreciated
EXAMPLE 1
DIMOVA, ADRIANA
NONREF 4353647465464336
/OCMT/EUR60,71/ORD.BANK/DEUTDEDBMUC/ORD.CUST/DIMOVA, ADRIANA/PAY
.DET./RECHNUNGNR. NL76549-10 BUSINESS-ID 76549"
EXAMPLE 2
CLAUDIA ZINAYIDA
TFR+
BE75491-25 BEAUTIQUE CRISTINA FERNAN DES
CHARGE WAIVED
I'm looking for a formula which will pick up the string "NL76549-10" in example 1 and "BE75491-25" in example 2.
I've tried a formula along the lines of
IF(FIND("NL",A1,20)-FIND("-",A1,FIND("NL",A1,20))=-7,MID(A1,FIND("NL",A1,20),10),"CHECK")
and combining the iferror function search for BE and LT however I run into issues when there are multiple instances of "NL" or "BE" or "LT" in the string and the reference number is not always the first or the last to contain the string.
Any help will be greatly appreciated