How to transform raw tabular data into new tabular format

[D_Y]

Hi everyone

I have data that looks like this

Inflation 5.00% Interest Rate 4.56%

Net Rental Profit 1,567,201 14.65 Discount Rate

How can I transform it so that the data instead appears like this

Inflation	5.00%
Interest Rate	4.56%
Net Rental Profit	1,567,201

Note: the 14.65 Discount Rate can be omitted/ignored but it can also be included in the above table if that is easier (and I can simply filter it out).

Thanks for any help!

[Pilsner · Accepted solution]

Hello,

I have used some regex to try and extract out the values which follow the structure of [value type] + [value] (i.e "Inflation" + "5.00%"). The ones that didn't matched have then been paired back up using a multi row formula which accounts for values that are in reverse order (such as "14.65" + "Discount Rate") . This is then separated out using another regex tool. 

Just a brief explanation, but as you will see, the regex used mainly references sets which look like this: [a-zA-Z\s] -  meaning any capital or lowercase character or space. By adding in a ^ the statement changes to anything that is not a lowercase or capital character or space. 

I believe this solution works for the example you provided but please let me know how you get on.

Regards - Pilsner

 

[alexnajm]

One option! Just four tools - feel free to add record IDs if needed

[D_Y]

Thank you!