Hi everyone,
Good day to all of you, i have a requirement to split string in line86, my current formula is taking in information from line61 as there is only 1 subtag in some lines in the file example:
:86:X02?20ABCD
:61:2345436436754,00CE02//CHQ
Result:
ABCD :61:2345436436754,00CE02//CHQ | | |
There will be some line with 4 Subtags with result below with my formula:
:86:R08?20CHH?21From: PETER & CO PTE LTD?22PayNow?23OTHER COMPANY NAME
:62F:242341234124,24 :64:2341412412423-}
Result:
CHH | From: PETER & CO PTE LTD | PayNow | OTHER COMPANY NAME :62F:242341234124,24 :64:2341412412423-}
How can I fix to accommodate for any number of subtags in line86 without taking information from other lines. Thank you so much
My formula
Hi Benjamin,
Because your formula is already too complex I would go step by step.
1 - Take out lines after the 86, with something like:
FIRST( SPLIT( Replace( @.Line86, ":[0-9]", "{[(NEWLINE)]}", true), "{[(NEWLINE)]})" )
Another way to do it is to modify Microsoft own formula, and make sure it only takes the first line that starts by 86, I can't test it but it may look something like:
1 - Split by new line
SPLIT( REPLACE( format.StatementFile.Statement.BankStmtLine.VALUE , "(\r\n|\r|\n):" , "{[(NEWLINE)]}:", true), "{[(NEWLINE)]}" )
And then just take the first (or the last like MS with reverse) line that starts by 86:
FIRST(REVERSE(WHERE(@.PreviousStep,LEFT(@.PreviousStep.Value,4)=":86:")))
I'm sure I've made a lot of mistakes, but I hope you get the idea. Solution 1 is faster, 2 is cleaner. And of course after the transformation you have to point to this new node with your current formula.
André Arnaud de Cal...
291,971
Super User 2025 Season 1
Martin Dráb
230,846
Most Valuable Professional
nmaenpaa
101,156