RegEx to Parse sentence

Hi SME,

I have "SUI Wages (Subject)|11141488Y|2854788Q|SUI Wages (Taxable)|700000Y|Q|SDI Wages|11141488Y|2854788Q" this as a sentence where the delimiter is "|", and I am not understanding how to parse the word and numbers where I want the numbers as "111414.88" where last two numbers will be our decimlas and if we have only "Q" or "Y" without any values in front of them which will be "0". I would like to know if it's possible to use the RegEx tool to parse or any other tools that can make it dynamic.

Thanks,

SR.

Regex

Parse

Accepted answers

binu_acs

@Peddsairam one way of doing this

Parse_Data_28072025.yxmd

All comments

OTrieger

@Peddsairam
You can use Regex to parse it. I think that it will be simpler with Text to Columns tool. Nevertheless you can choose either of these tools to get the desired results.

For 2 decimals you will need to use additional tool, such as formula tool where you will divide the number by 100 and then set a fixed delimiter data type to ensure that you will always have 2.

binu_acs

@Peddsairam one way of doing this

Parse_Data_28072025.yxmd

Pilsner

Hello @Peddsairam

Yes, you can use regex to help you with this problem; however, to make this fully dynamic, I would use a couple of other tools too.

Firstly, use the regex to split each repetition of the pattern into different rows:

Then use the the text to columns to split the data based on the | delimiter.

To format the numbers I would simply trim the Y and Q from the end, then divide the numbers by 100.

Finally you can use the select tool to drop and rename columns.

Screenshot 2025-07-28 095930.png

If you want the data all on the same row, you can use a cross tab tool to help however I have left the data in the table format.

I've attached the workflow below to help further.

Please let me know if you have any questions.

Regards - Pilsner

New Workflow1.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703