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]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR] |
car661@msn.comangrymen@comcast.netdlarter@email.itt-tech.educristinaschlundt@gmail.combradpalecek@edinarealty.comrachelchampion82@gmail.com |
|
|
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR] |
car661@msn.com |
angrymen@comcast.net |
dlarter@email.itt-tech.edu |
cristinaschlundt@gmail.com |
bradpalecek@edinarealty.com |
rachelchampion82@gmail.com |
<tbody>
</tbody>
you can add more domains if necessary