RE: MT940 Bank statement format setup line86 formula for pulling additional information
Hi Benjamin,
I didn't test this formula but it would look something like:
IF
(
CONTAINS(@.String,"?20"),
FIRST(SPLIT(INDEX(SPLIT(@.String,"?20"),2).Value,"?")).Value,
""
)
& " | " &
IF
(
CONTAINS(@.String,"?21"),
FIRST(SPLIT(INDEX(SPLIT(@.String,"?21"),2).Value,"?")).Value,
""
) ...
Basically the first IF looks if "?20" exists, if it does it splits the string by "?20" and takes the second part, this part is split by "?" and takes the first part.
Then is concatenation and doing the same for 21, 22....
Maybe in the text there is a "?" that's not a separator, normally it's escaped with a character, not sure about M940 though. if it was the case that a "??" means a real "?" and not a separator you could do a previous step Replace(@.String, "??", "<TEMP>","false") and after split you convert back to a single "?"...
Here the documentation for formula functions:
learn.microsoft.com/.../er-formula-language
