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.
Solved! Go to Solution.
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 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
@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 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.
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.