How to remove Prefix & Suffix from the raw data

[Rajeev18]

Hello Team,

I want to remove Prefix & Suffix from the raw data. Output data should data after removing it. In this example i want product id withwithout collon data

Input Data is like below: Prefix & removal to apply on Product ID.

Product ID	Product Name	1st level Approval status	Date
17I:634534:V9	C	WIP	2019-10-29
17I:3019IA:V2	W	Submitted	2019-12-11
18I:423123:V1	s	Accepted	2019-12-13
18I:1323451:V2	q	In Transmit	2020-01-10
19I:98765:V3	r	In Transmit	2020-01-16
19I:234567:V1	e	Submitted	2019-12-11
19I:234987:V4	u	In Transmit	2019-10-27
343011_2018	W	Submitted	2019-12-11
12345-2019	s	Accepted	2019-12-13

Output Data required like below:-

Product ID	Product Name	1st level Approval status	Date
634534	C	WIP	2019-10-29
3019IA	W	Submitted	2019-12-11
423123	s	Accepted	2019-12-13
1323451	q	In Transmit	2020-01-10
98765	r	In Transmit	2020-01-16
234567	e	Submitted	2019-12-11
234987	u	In Transmit	2019-10-27
343011	W	Submitted	2019-12-11
12345	s	Accepted	2019-12-13

[AbhilashR]

Hi @Rajeev18, you could use the regex tool and parse the product ID field.

The Regular Expression in this case would be:     \:(.*?)\:

Hope this helps!

[afv2688 · Accepted solution]

Hello @Rajeev18 ,

would this help?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[Rajeev18]

Wit this only the punctuations will be removed. I want along with punctuations number and letter which are supporting that punctuations also to be removed. 

Like 
if the data is this way - 19|:634534:V1  -- - Output data required - 634534.

[afv2688]

Are you talking about my solution? It removes all the required suffixes and prefixes

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[AbhilashR]

@Rajeev18 - I initially didnt pay attention to the different combinations of Product ID you had, sorry. Take a look at the attached file and see if this suffices your request.

My approach is basically looking to identify different combinations of Product ID you have, and find the right regular expression to parse the data. Once I identified the string, I used a formula tool downstream to unify the product ID list.

For your case, the expression I used is  \:(.*?)\:|^(.+?)-|^(.+?)_

 \:(.*?)\: helps parse ID that look like 17I:634534:V9

^(.+?)- helps parse ID that look like 12345-2019

^(.+?)_ helps parse ID that look like 343011_2018

Hope this helps!

[Simha]

Please see attached solution using RegEx. You can further modify according to your requirements.