Separate Junk Emails

[Blessy Clara]

There is an excel sheet with Emails in a column named EMAIL

there are multiple emails separated by "||"

I want to retain the valid emails - in one column and separate the not so good emails /data to another column
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]3SI@12T.QMNgC || FFo@a46ZZi.g || ri@80.jiBjX0G || Z9G@85YU.Pb || U@ZT.VLn0iplKH || 2olXA@D6.m7 || 5I4m@j5MRsUH.dU || Ym@PI.ZaGMIM..O || Ee@pjt.RM6u || VZr@6.XsmCZKIlLF-ogW || sI2@q-bK.bd1ha.pqMp || Ykr5@.YW.1UN || Hagrr@9.I6Q || M9Pt@XTGN.8 || _@.Z22NMRsUH.dU0fX0G || A3Oi@_hL.75nIOC || 2YXgWO@qIPuqX.Cdlm || 3c3OZZO@_.pGCXe || Wgq@gVFe.Edu9 || aD@d0C_.6UY || t5UP@je4W.uL.6QA || J@C1Cse_Ll.l || Jp@E0TVIfF4G.4 || ZcE@ul.k9sS5n4BYubD || SPVg5njo@ASh.g || 7Q@O.OkHQiP || _K.7IoSsTI@0R.jE1KQ7L3srh || VZ@PnogJYBFM.K || msg7@b6RV.fs4YHH_ || k06A0@TcX.Z || steve@slingshotstudios.com.au || G@9.sG_sbrlp3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5.2K@4.2Gbps || sales@metrohobbies.com.au[/TD]
[/TR]
</tbody>[/TABLE]

Post Code
[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]3SI@12T.QMNgC || henry@bookkeepingconcepts.com.au||FFo@a46ZZi.g || ri@80.jiBjX0G || Z9G@85YU.Pb || U@ZT.VLn0iplKH || 2olXA@D6.m7 || 5I4m@j5MRsUH.dU || Ym@PI.ZaGMIM..O || Ee@pjt.RM6u || VZr@6.XsmCZKIlLF-ogW || sI2@q-bK.bd1ha.pqMp || Ykr5@.YW.1UN || Hagrr@9.I6Q || M9Pt@XTGN.8 || _@.Z22NMRsUH.dU0fX0G || A3Oi@_hL.75nIOC || 2YXgWO@qIPuqX.Cdlm || 3c3OZZO@_.pGCXe || Wgq@gVFe.Edu9 || aD@d0C_.6UY || t5UP@je4W.uL.6QA || J@C1Cse_Ll.l || Jp@E0TVIfF4G.4 || ZcE@ul.k9sS5n4BYubD || SPVg5njo@ASh.g || 7Q@O.OkHQiP || _K.7IoSsTI@0R.jE1KQ7L3srh || VZ@PnogJYBFM.K || msg7@b6RV.fs4YHH_ || k06A0@TcX.Z || steve@slingshotstudios.com.au || G@9.sG_sbrlp3[/TD]
[TD]steve@slingshotstudios.com.au ||
henry@bookkeepingconcepts.com.au[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5.2K@4.2Gbps[/TD]
[TD]sales@metrohobbies.com.au[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]JUNK[/TD]
[TD]VALID Emails[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Criteria for JUNK - Doesnt end with
.au/.com/.edu/.net/.org/.biz

Thanks in advance

 

[ClaireS]

Use Data, Get and Transform. I used your sample and went through these steps (I could certainly improve on its efficiency by being more careful about the order I do things):

• Transposed the table
• Removed Blank Rows
• Cleaned Text
• Trimmed Text
• Renamed Columns
• Added a new Conditional Column to get the "good" addresses
• Added a new Conditional column to show nothing if there was an entry in this row of the Good email addresses, otherwise show the original column entry
• Removed the original column
• Reordered the columns to have "Probable Junk" on the left and "Good email" on the right
• Sorted into ascending order of Probable junk (put blank cells, i.e. Good emails to the top)

The final M Code in the Advanced Query Editor was this (though I did it all by operating Power Query):

[FONT=Verdana][FONT=Verdana]let
    Source = Csv.Document(File.Contents("C:\Users\ [ … path goes here … ] \Emails.txt"),[Delimiter="|", Columns=63, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Removed Blank Rows" = Table.SelectRows(#"Transposed Table", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Cleaned Text" = Table.TransformColumns(#"Removed Blank Rows",{{"Column1", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"Column1", "Emails"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Good email", each if Text.EndsWith([Emails], ".au") then [Emails] else if Text.EndsWith([Emails], ".com") then [Emails] else if Text.EndsWith([Emails], ".edu") then [Emails] else if Text.EndsWith([Emails], ".net") then [Emails] else if Text.EndsWith([Emails], ".biz") then [Emails] else if Text.EndsWith([Emails], ".org") then [Emails] else ""),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Probable Junk", each if [Emails] = [Good email] then "" else [Emails]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Emails"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Probable Junk", "Good email"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Probable Junk", Order.Ascending}})
in
    #"Sorted Rows"[/FONT][/FONT]<strike></strike>

Good luck!

 

[Blessy Clara]

Hi ClaireS

Thank you for your reply and efforts - But could you please help me use this code - I copy pasted this code in view code and it is all highlighted in red .. and not able to use this

 

[ClaireS]

This is not VBA code - did you paste it in there? It goes into the Advanced Query Editor in Power Query.