Reg Ex needed for Bank description

Question

Hi all,

We have a workflow that pulled receiving bank account numbers from a description cell string on an exported bank statement. to simplify :

Old Description on BS :

"BAI=575;YOUR REF=ZERO BALANCING;REMARK=/VXR/4000054089/SWEEP REF To: 6550209744 USD J.P. Morgan SE - Fr: 6550193181 USD J.P. Morgan SE - Intercompany LPQ655315214352 To: Joxer UK Limite Fr: Joxer Ireland"

Reg Ex to pull the "To" account number and USD was "SWEEP\sREF\sT[a-zA-Z]:\s(\d+)\s(USD)"

The New description on the Bank Statements is :

"BAI=575;YOUR REF=ZERO BALANCING;REMARK=/VXR/4000051820/SWEEP REF /RFB/4000051810/ TO: 6550193181 USD J.P. MORGAN SE - FR: 0142505776 USD TOKYO, JAPAN INTERCOMPANY LPQ65531842881 TO: Joxer IRELAND FR: Joxer JAPAN G.K."

Issue : I need to update the Reg Ex to pull the "To" account number in red. Old one no longer works as there has been more descriptive text added (/RFB/4000051810/) which disrupts the matching.

I hope this makes sense !

thanks.

flying008 · Answer

Hi, @kmurphy1980

(?:SWEEP\sREF.+?:\s?)([\d]+?)(?=\sUSD)

Amy_smart · Answer

Hi,

Try this REGEX with a Parse

^.+?:\s(.+?)\s.+

It will just output your number.

Thanks,

Amy

kmurphy1980 · Answer

Thank you so much @binuacs !

another way !