RegEx to Parse sentence

[Peddsairam]

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.

[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.

[binuacs · Accepted solution]

@Peddsairam one way of doing this

[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. 

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