How to transform raw tabular data into new tabular format

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!

Parse

Transformation

Accepted answers

Pilsner

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

Screenshot 2025-01-02 165721.png

Example 1.yxmd

All comments

Pilsner

Screenshot 2025-01-02 165721.png

Example 1.yxmd

alexnajm

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

How to transform raw tabular data into new tabular format.yxmd

D_Y

Thank you!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335