Forum Discussion

kbudzynski's avatar
kbudzynski
Regular Visitor
4 years ago
Solved

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 spa...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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] )
    )
    )
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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.