Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Extract name from email

I am trying to create a custom column that extracts the name from the email address.  I need to remove both the domain name and the . between the first and last name and then i was going to have it c...
Need Help
  • FrankAT's avatar
    FrankAT
    5 years ago

    Hi Anonymous ,

    if you have a single value like [email protected] you can use the following M-code

     

    = Text.Proper(Text.BeforeDelimiter(Text.Replace("[email protected]", ".", " "),"@"))

     

    to get this:

     

     

    If you have a column in a table use the following M-code:

     

    // Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkgtSS3SS0rMyXFIyc9NzMzTK83WSyxVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-Mail Address" = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"E-Mail Address", each Text.BeforeDelimiter(_, "@"), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter","."," ",Replacer.ReplaceText,{"E-Mail Address"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"E-Mail Address", Text.Proper, type text}})
in
    #"Capitalized Each Word"

     

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

     

     

     

     

     

     

FrankAT's avatar
FrankAT
Community Champion
5 years ago

Hi Anonymous ,

if you have a single value like [email protected] you can use the following M-code

 

= Text.Proper(Text.BeforeDelimiter(Text.Replace("[email protected]", ".", " "),"@"))

 

to get this:

 

 

If you have a column in a table use the following M-code:

 

// Data
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkgtSS3SS0rMyXFIyc9NzMzTK83WSyxVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-Mail Address" = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"E-Mail Address", each Text.BeforeDelimiter(_, "@"), type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Text Before Delimiter","."," ",Replacer.ReplaceText,{"E-Mail Address"}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"E-Mail Address", Text.Proper, type text}})
in
    #"Capitalized Each Word"

 

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)