Remove pipe | after @ in email

Hi

How can I remove the pipe delimiter from my email address?

For example - some records have this kind of email

johndoe@|yahoo.com

Pipe | is also my file delimiter so while processing the email address gets broken up into two parts (before and after the |) and workflow stops execution giving an error like Too many fields on record #7876. Please help.

Thanks

Email

Accepted answers

If you have embedded pipes in your email address and these fields are not text qualified then I think you will have to read your data in and specify no delimiter in option number 5 a backslash followed by a zero denotes that AKA "\0":

Then you can use a formula like mentioned in the above post update it to replace the pipe when it is after the @ symbol.

REPLACE([Field_1],"@|","@)

Then we need to use a text to columns to separate out the clean data into separate fields.

Lastly if you have a header you may want to use the dynamic rename to update the field names.

I have attached the workflow that looks like:

Hopefully this will achieve the output that you want once you connect up your data. Please note you will need to update the number of columns for the Text to Columns tool for how many your data contains.

EmbeddedPipe.yxzp

All comments

Adding a formula tool with the following formula should do the trick:

REPLACECHAR([email],"|","")

Hope that helps!

NJ

If you have embedded pipes in your email address and these fields are not text qualified then I think you will have to read your data in and specify no delimiter in option number 5 a backslash followed by a zero denotes that AKA "\0":

Then you can use a formula like mentioned in the above post update it to replace the pipe when it is after the @ symbol.

REPLACE([Field_1],"@|","@)

Then we need to use a text to columns to separate out the clean data into separate fields.

Lastly if you have a header you may want to use the dynamic rename to update the field names.

I have attached the workflow that looks like:

Hopefully this will achieve the output that you want once you connect up your data. Please note you will need to update the number of columns for the Text to Columns tool for how many your data contains.

EmbeddedPipe.yxzp

I just wanted to throw another thought into the thread...

If you have data that is not only delimited, but also quoted...

"Frisch"|"Mark"|"mark@|marqueecrew.com"

Then you can ignore delimiters that are within the quotes. After importing, you can replace the '|' pipe or other symbols as needed.

Cheers,

Mark

Nicole , thanks for replying. Unfortunately that didn't solve the problem.

Quick Links

Tags

This months top contributors

Hollingsworth 335