RegEx - specific expression searched

[Newbee3]

Hey everyone

I would - once more - highly appreciate your help on following request. I need to extract certain data from a string. Please find below some sample data extracts:

Raw data	target extract
Deposits-Q	Q
Deposits-1Q20	1Q20
Short-term borrowings-6M19	6M19
Trading liabilities-6M19	6M19
Central bank funds purchased, securities sold under repurchase agreements and securities lending transactions-2Q19	2Q19
Other-Q-1q	Q-1q
of which loans held-for-sale reported at lower of cost and market value (amortized cost base)-Q-1q	Q-1q
Obligation to return securities received as collateral, at fair value-Q-2q	Q-2q
Additional paid-in capital-Q	Q

I would highly appreciate if you could provide me with the respective RegEx function. Currently I am going with "REGEX_Replace([annual/quarterly report], "(.)*-Q", 'Q')" which is not working properly as you can guess. Due to the complex structure of the workflow I would appreciate - if possible - that the solution focuses on the RegEx_replace function rather than a work around.

Thank you very much upfront.

[jdunkerley79 · Accepted solution]

Based on your sample, a regular expression of:

.*?-(\d*[QM](\d*|[-]\d+q))$

should get the value you want into $1.

• First it no greedily matches the start of the string
• Looks for a - followed by either numbers then a Q/M or just a Q/M (\d*[QM])
• Then it allows a choice either possibly numbers (\d*) or a block starting with a minus then some numbers and then a q ([-]\d+q)
• The last $ makes it match until the end of the string

So a formula of:

REGEX_Replace([ Raw data], ".*?-(\d*[QM](\d*|[-]\d+q))$", "$1")

should work

Have attached a sample. 

[atcodedog05]

Hi @Newbee3 ,

Here is workflow for the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Newbee3]

okay, I guess I am a bit blown away 'caus:

1. the response was super fast

2. the regex is really complex

Would not have been able to solve this myself! Thank you!

[atcodedog05]

I agree on that part @jdunkerley79  is amazing with Regex 😎