Interchange string value and replace

[Kaish]

Hi All, I have a requirement where a data comes from a particular source the data is not in the correct form, below is the example for the requirement.

input: D1_A12345_MN,  D1_A12875_MN,  D1_A34234_AP

output: MNA12345 , MNA12875 , APA34234

So basically what happened here some of the data comes in this format and others are okay which have only numbers, but when there are alpha numeric data it gives this D1_A12345_MN for example,  here I want to remove D1_ from the start and then add the last 2 alphabets to the start which is MN and removing other symbols like _ so the final output be MNA12345 . Can someone help to achieve this please. I am stuck

[DavidSkaife]

Hi @Kaish 

Here is a way of doing it using the Formula tool. Create a temp column which captures the last two characters of the string, then remove the first 3 and last 3 characters from the input, then combine the temp field back onto the input. Use a Select tool to remove the temp column.

If you have any inputs that don't match the combination you've detailed, then you'd need to filter them out, and join back - but this should give you an indication of one way of performing what you're after

[Kaish]

Hi @DavidSkaife , Thanks for your reply, this will surely work on the data but here I have one challenge, I have other rows of data as well which are in the exact form, so I want to work on only those data which have this attribute which starts with particular format which starts with D1_ 

[DavidSkaife · Accepted solution]

In that case then add in a Filter tool first, filtering out where the record starts with D1_ then complete the changes, and Union the datastreams back together