Parse Data

[DataPirate26]

Hi All,

Below I have attached one dataset which has one Field as Input.

example : AC:PROCESSING-USR00035149. I have to parse out the Bold part from this. 

Note: If you see the dataset the values are not in any standardised pattern and in different rows they are present in a different way. From all the rows we have to Parse out this similar Part. Can someone help me out with this ?

below attached is a sample data. 

[Jotigautam · Accepted solution]

Attached is a solution using regex. Let me know if this serves the purpose.

[flying008]

Hi, @DataPirate26 

As red box , what are you want output?

Input	Parse	Replace	Diff
AC:PROCESSING-USR00035149	USR00035149	USR00035149
AC:COORDINATION-USR00035149	USR00035149	USR00035149
AC:COORDINATION-USP99999999	USP99999999	USP99999999
AC:COORDINATION-USP99999999	USP99999999	USP99999999
AC:COORDINATION-USP99999999	USP99999999	USP99999999
AC:COORDINATION-USP00509575	USP00509575	USP00509575
AC:COORDINATION-USP00688914	USP00688914	USP00688914
AC:MGR REVIEW-USR00027097	USR00027097	USR00027097
AC:FIRST REVIEW-USR00026669 IN PROG	USR00026669	USR00026669
AC:COORDINATION-USR00026669	USR00026669	USR00026669
AC:MGR REVIEW-USR00027097	USR00027097	USR00027097
AC:MGR REVIEW-USR00029116 IN PROG	USR00029116	USR00029116
AC:MGR REVIEW-USR00029118 IN PROG	USR00029118	USR00029118
AC:FIRST REVIEW-USR00026669 IN PROG	USR00026669	USR00026669
AC:MGR REVIEW-USR00027097 IN PROG	USR00027097	USR00027097
AC:COORDINATION--USR00029116	USR00029116	USR00029116
AC:MGR REVIEW-USR00029116 IN PROG	USR00029116	USR00029116
AC:MGR REVIEW-USR00029116 IN PROG	USR00029116	USR00029116
AC:REVIEW-USR00026669	USR00026669	USR00026669
AC:FIRST REVIEW-USR00026669 IN PROG	USR00026669	USR00026669
AC:COORDINATION-USR00029116	USR00029116	USR00029116
AC:MGR REVIEW-USR00029116	USR00029116	USR00029116
AC:MGR REVIEW-USR00029116	USR00029116	USR00029116
AC:REVIEW-USR00038013	USR00038013	USR00038013
AC:REVIEW-USR00035195	USR00035195	USR00035195
AC:REVIEW-USR00038013	USR00038013	USR00038013
AC:REVIEW-USR00037183	USR00037183	USR00037183
AC:REVIEW-USR00037722	USR00037722	USR00037722
AC:REVIEW-USR00035195	USR00035195	USR00035195
AC:PROCESSING-USR00031950	USR00031950	USR00031950
AC:PROCESSING-USR00031950	USR00031950	USR00031950
AC:COORDINATION-USP00463022	USP00463022	USP00463022
AC:FIRST REVIEW-USP00585852	USP00585852	USP00585852
AC:PROCESSING-WRAAT01-B46	WRAAT01	WRAAT01B46	-
AC:FIRST REVIEW-USP00540312	USP00540312	USP00540312
AC:FIRST REVIEW-USP00506143	USP00506143	USP00506143
AC:COORDINATION-2000102252-B53	2000102252	2000102252B53	-
AC:PROCESSING-2000108275-B53	2000108275	2000108275B53	-
AC:REVIEW-USP00694019-B39	USP00694019	USP00694019B39	-
AC:PROCESSING-USR00028397- DS	USR00028397	USR00028397
AC:FIRST REVIEW-USR00027097-S&R IN PROGR	USR00027097	USR00027097
AC:FIRST REVIEW-USR00027097-S&R IN PROGR	USR00027097	USR00027097
AC:FIRST REVIEW-USR00027097-S&R IN PROGR	USR00027097	USR00027097
AC:PROCESSING-USR00035120 PROCESS CHECK	USR00035120	USR00035120
AC:PROCESSING-USR00039634 PROCESS CHECK	USR00039634	USR00039634

So, If you just want to get 10 consecutive characters (please see the filed [Parse] ), use Parse method by RegEx tool.

(?<=\-)(\w+)(?=-|\s|$)

******

If this syntax can help you , please mark it as a solution for share more.

[binuacs · Accepted solution]

@DataPirate26 Another way of doing this withe the regex tokenize method

[DataPirate26]

@binuacs The solution worked Fine. Can we modify the formula to parse out the Review , Processing and Coordination part as well in a separate field. Can you help with this 

[DataPirate26]

@flying008 In the red part I only need the USP and USR part not the Last two letters. Also along with that I need a separate Field which will have the the word Review, Processing or Coordination parsed out from the Input String. I would need it in a separate field beside the Output. 

[binuacs · Accepted solution]

@DataPirate26 Updated the workflow

[DataPirate26]

@binuacs Thanx for the quick response. Worked perfectly fine. I could have used a formula to Create a new field but this is a much more efficient solution. Appreciate it. 

[flying008 · Accepted solution]

Hi, @DataPirate26 

Don't worry, please use the syntax with parse method by RegEx tool;

AC\:([A-Z|\s]+)-(?<=\-)(\w+)(?=-|\s|$)

******

If this syntax can help you , please mark it as a solution for share more.