Sandy, First, let me state that I am not skilled when it comes to excel. Here's what I pasted into a module for macro.:
Sub one()Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"src", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".com",".com^",Replacer.ReplaceText,{"src"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".edu",".edu^",Replacer.ReplaceText,{"src"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".net",".net^",Replacer.ReplaceText,{"src"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value2",{{"src", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Trimmed Text", {{"src", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "src"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"src", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",1)
in
#"Removed Bottom Rows"
End Sub
I selected the first cell in column a and ran the macro and it put up a window that said "Compile error" what is wrong with my process??
Also, I notice that if I paste say, 50 of these combined emails, they ARE SEPARATED when in developer mode, and no macro running, but if I post the whole list of 27,000, it defaults to putting it all in cell 1
with your example
via PowerQuery
Code:
[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"src", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".com",".com^",Replacer.ReplaceText,{"src"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".edu",".edu^",Replacer.ReplaceText,{"src"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".net",".net^",Replacer.ReplaceText,{"src"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value2",{{"src", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Trimmed Text", {{"src", Splitter.SplitTextByDelimiter("^", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "src"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"src", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",1)
in
#"Removed Bottom Rows"[/SIZE]
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]car661@msn.comangrymen@comcast.netdlarter@email.itt-tech.educristinaschlundt@gmail.combradpalecek@edinarealty.comrachelchampion82@gmail.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]car661@msn.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]angrymen@comcast.net[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]dlarter@email.itt-tech.edu[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]cristinaschlundt@gmail.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]bradpalecek@edinarealty.com[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]rachelchampion82@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]
you can add more domains if necessary