Forum Discussion
Grabbing email strings
Hello,
I have a text field that has numerous email strings in it. I want to remove everything but the email addresse(s)--(basically all strings that encompass @ and keep all characters to the spaces on each side). In some fields, there are numerous emails Can anyone suggest a solution? I have Googled and tried many things without any luck.
Many thanks for any help!
Kev
Hi kbudzynski,
You can create a Calculated Column with the following. Just change your Table and Column name 🙂
Where there is more than one email in a single text string, I'd recommend using Power Query to split up the strings so that the Calculated Column doesn't return an error, then repeat the process accordingly.
All the best! 🙂
Just in case screen shot is blurry, below is the Calculated Column:
Email Clean =TRIM (RIGHT (SUBSTITUTE (LEFT ( 'Table'[Email] ,FIND (" ",'Table'[Email] & " " , FIND ( "@" , 'Table'[Email] ) ) -1) , " " , REPT (" " , LEN ( 'Table'[Email] ) ) ) , LEN ( 'Table'[Email] )))In the query editor, you can use the Text.Split function to make a list of all the strings, using the space " " as the delimiter, and then list List.Select to keep only the list items that Text.Contain the "@" symbol. From there, you can expand that list to new rows, or use Text.Combine to re-concatenate the email addresses with ";" or another delimiter.
Pat
4 Replies
Hi kbudzynski,
You can create a Calculated Column with the following. Just change your Table and Column name 🙂
Where there is more than one email in a single text string, I'd recommend using Power Query to split up the strings so that the Calculated Column doesn't return an error, then repeat the process accordingly.
All the best! 🙂
Just in case screen shot is blurry, below is the Calculated Column:
Email Clean =TRIM (RIGHT (SUBSTITUTE (LEFT ( 'Table'[Email] ,FIND (" ",'Table'[Email] & " " , FIND ( "@" , 'Table'[Email] ) ) -1) , " " , REPT (" " , LEN ( 'Table'[Email] ) ) ) , LEN ( 'Table'[Email] )))Many thanks for you 3. These solutions work and many, many thanks.
Can you give some example inputs and outputs?
If you just need to remove rows that don't have an "@" symbol, things are simple. If you have strings with multiple emails that also have other text you need to remove while still retaining all of the emails, then things will be much more difficult.
In the query editor, you can use the Text.Split function to make a list of all the strings, using the space " " as the delimiter, and then list List.Select to keep only the list items that Text.Contain the "@" symbol. From there, you can expand that list to new rows, or use Text.Combine to re-concatenate the email addresses with ";" or another delimiter.
Pat
