Parsing The Data

[shashank_shukla]

Hello,

Scenario:-  I am fetching one column from database which contains the Comments and from the comments I have to only fetch the specifics words and order of the words is not fixed only base of some keywords I have to fetch the data.

Ex:- 

Input	Output1	output2
I am working on [Issue:0] and the result is [Invalidreason:Not submitted]	0	Not submitted
I am working on [Issue:1] and the result is [Invalidreason:Resolved]	1	Resolved
I am working on [Invalidreason:Transfer]  and the result is [Issue:3]	3	Transfer

The example is mention the same way I am getting the data. The order of [Issue:...] and [invalidreason:......] is not fixed.

Can anyone help me to find out the solution.

If possible kindly attach the sample solution as well.

Thanks And Regards,

SHASHANK R. SHUKLA

[DataNath]

I'm a little confused. So you currently have just the input and want to know how you can get the Output1/Output2 columns?

[shashank_shukla]

Hello @DataNath ,

Yes, I want a solution for how to get output1 and output2.

The Output columns are the new columns and value has to fetch from Input column.

[DataNath · Accepted solution]

Thanks for the extra detail!

Would this work? Workbook attached.

[OllieClarke · Accepted solution]

Hey @shashank_shukla 

You can use RegEx to do this. Use two RegEx tools both in parse mode with this expression for Issue:

\[Issue:(/d+)\]

This parses all numbers immediately after "[Issue:" and immediately before "]"

And this expression for Reason:

\[Invalidreason:(.+?)\]

This parses everything immediately after "[Invalidreason:" until the next "]"

Hope that helps,

Ollie

[binuacs · Accepted solution]

@shashank_shukla another way of dong this with the formula tool 

output1 - REGEX_Replace([Input], '[^\d]+', '')

output2 - REGEX_Replace([Input], '.*\[Invalidreason:(.+?)\].*', '$1')

[OllieClarke]

Hey @binuacs I'd be wary of the RegEx you've used for Output1, it's not unfeasible that the comment field could have numbers in it other than the issue number, which would confuse this RegEx

[binuacs · Accepted solution]

@OllieClarke I agree with you. I  updated regex for input1 

output1 - REGEX_Replace([Input], '.*\[Issue:(\d+)\].*', '$1')

output2 - REGEX_Replace([Input], '.*\[Invalidreason:(.+?)\].*', '$1')

[shashank_shukla]

Thanks @binuacs ,

For your quick response its means a lot.

Your solution is working fine for me.

[shashank_shukla]

Thanks @DataNath 

For your quick response its means a lot.

Your solution is working fine for me.