MT940 statement format setup line 61 formula for pulling bank document number

(0) Share

Report

Posted on by Benjamin long

Hi everyone,

Good day to all, i am trying to update existing ER design formula to get document number after "//" as my MT940 file line 61 contains document number but some lines will contain characters. Example
:61:2304201111CD11,80NTRFTest company//01359
Result:01359
:61:2304201111CD11,80NTRFTest company//DOC1234
Result: 1234
:61:2304201111CD11,-//MMB11289578698201?12345678
Result: 11289578698
:61:2304030678OC14,40NTRFTesting Company//T67584965789201?123456
Result:67584965789

My current formula:

IF(CONTAINS(@.Line61, "//"), RIGHT(FIRST(SPLIT(INDEX(SPLIT(@.Line61, "//"), 2).VALUE, "201?")).VALUE, Len(FIRST(SPLIT(INDEX(SPLIT(@.Line61, "//"), 2).VALUE, "201?")).VALUE) - 3), "")

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

huijij 19,811 on at

Like (1)

Report

RE: MT940 statement format setup line 61 formula for pulling bank document number

Hi sir,

You can start with a small part of the function and test it, then see what the real problems are.

Was this reply helpful? Yes No
Benjamin long 40 on at

Like (0)

Report

RE: MT940 statement format setup line 61 formula for pulling bank document number

Hi Judy,

Thank you so much for your response, I manage to get the formula to work.

IF(CONTAINS(@.Line61, "//"), REPLACE(FIRST(SPLIT(INDEX(SPLIT(@.Line61, "//"), 2).VALUE, "??")).VALUE, "[^0-9]", "", true), "")

Was this reply helpful? Yes No