Help with removing specific data from a string

[EswaraRajitha]

Hi

I have a string data in the below format

sairam9@gmail.com|Ram3@gmail.com|srilatha6@gmail.com|guru45@gmail.com

And i have a list of valid email address like below

1.sairam9@gmail.com

2.guru45@gmail.com

So i would like to check each email address whether it is there in the valid list and keep only that data

Output data should be as below

sairam9@gmail.com|guru45@gmail.com

I tried find and replace but it is taking longer time for processing the data

Can anyone of you suggest me the best practice so that i could achieve it

[Assaf_m]

Check attached solution

[mceleavey]

Hi @EswaraRajitha ,

This is the perfect use case for the Find and Replace tool:

This searches the text string for the emails and returns the matched email in an additional column.

Workflow attached.

I hope this helps.

M

[Qiu]

@EswaraRajitha 

Similar with others reply.

[mceleavey]

Hi @EswaraRajitha ,

In addition, there's a simple concatenation method which pivots the data out into rows, then concatenates into a cell separated by pipe:

M.