Need to separate email addresses, where they have no separation!

[larrylime]

Example is below. Which was pasted from a word doc copy. If I post the copied text in gmail, it does the separation! But if I try to post the list in excel, it puts all the content into one cell. Maybe there is some prepasting setup I can do in excel before pasting that would do the separation??? Or, HOW CAN I GET THESE SEPARATED!



robperez1@bellsouth.netorigamifleet@gmail.comnaquanchance@yahoo.comjeanpierrefica@gmail.commairatraders@gmail.comrp12520@gmail.com

 

[Leith Ross]

Hello larrylime,

It would help greatly to see the original text in the Word document. Can you post a copy to a file sharing site?

 

[larrylime]

Hello larrylime,

It would help greatly to see the original text in the Word document. Can you post a copy to a file sharing site?

Leith, I'm not sure the word doc is relevant? I copied the emails from a list on a site and pasted it to word to view it there. For some reason when I post it to gmail it makes the separations, so I assume they are looking for ".com" or whatever. Can excel formula do the same thing? Below I pasted a few, and it shows the color variation just like from the original source. Once i post here, it changes and you will see each one in a box, which I assume is the published variation of the diff color boxes originally. If I paste too many in here, it shows no color variant, just a continuous stream of connected words(name+address)

[TABLE="class: table table-striped table-bordered, width: 369"]
<tbody>[TR]
[TD="bgcolor: #F9F9F9"]car661@msn.com[/TD]
[/TR]
[TR]
[TD]angrymen@comcast.net[/TD]
[/TR]
[TR]
[TD="bgcolor: #F9F9F9"]dlarter@email.itt-tech.edu[/TD]
[/TR]
[TR]
[TD]cristinaschlundt@gmail.com[/TD]
[/TR]
[TR]
[TD="bgcolor: #F9F9F9"]bradpalecek@edinarealty.com[/TD]
[/TR]
[TR]
[TD]rachelchampion82@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
You could use the Excel built-in TEXT TO COLUMNS located on the DATA menu selection.

Use the period symbol ( . ) as the delimiter when asked which delimiter.

 

[larrylime]

.
You could use the Excel built-in TEXT TO COLUMNS located on the DATA menu selection.

Use the period symbol ( . ) as the delimiter when asked which delimiter.

Thanks, but I checked that and it won't work. it would end up: larry@gmail comJames@gmail etc

 

[Logit]

.
You right. Sorry.

Hmmm ....

 

[Logit]

.
Ok. This is definitely the LONG way around the corner to get to the goal .. but it works. Anyone else who desires to reduce the coding to something more concise ... please do.

Paste this into a ROUTINE MODULE:

Option Explicit


Sub replaceStringInCell()


    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/excel-vba-replace-substitute/


    'declare object variable to hold reference to cell you work with
    Dim myCell As Range


    'declare variables to hold parameters for string replacement (string to replace, replacement string, and number of replacements)
    Dim myStringToReplace As String
    Dim myReplacementString As String
    Dim myNumberOfReplacements As Long


    'identify cell you work with
    Set myCell = ActiveSheet.Range("A1")


    'specify parameters for string replacement (string to replace, replacement string, and number of replacements)
    myStringToReplace = "com."
    myReplacementString = "com/"
    myNumberOfReplacements = 300


    'replace string in cell you work with, and assign resulting string to Range.Value property of cell you work with
    myCell.Value = Replace(Expression:=myCell.Value, Find:=myStringToReplace, Replace:=myReplacementString, Count:=myNumberOfReplacements)
Macro1
End Sub


Sub Macro1()
'
' Macro1 Macro
'


'
    Range("A1").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
ConvertRangeToColumn
End Sub




Sub ConvertRangeToColumn()
'Updateby20131126
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
'xTitleId = "KutoolsforExcel"
Set Range1 = ActiveSheet.Range("A1:XFD1")
Set Range2 = ActiveSheet.Range("A3")
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

The above code reviews Row 1 on Sheet1 ... changes all the periods after the term "com" to a slash symbol ( / ). The macro then automates the TEXT TO COLUMNS procedure to separate all the email addresses to their own cell in Row 1.
The rest of the macro will transfer all data in Row1 to Col A.

You then have the choice of using all of Row 1 data ... or you can use all the email addresses now listed in Col A.

Download workbook : https://www.amazon.com/clouddrive/share/T35IR2U9s63LBCQ8IlGULla3KL4ppWsXg4HJW8WIIMb

 

[sandy666]

with your example

via PowerQuery

[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="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]car661@msn.comangrymen@comcast.netdlarter@email.itt-tech.educristinaschlundt@gmail.combradpalecek@edinarealty.comrachelchampion82@gmail.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]src[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]car661@msn.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]angrymen@comcast.net[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]dlarter@email.itt-tech.edu[/td][/tr]

[tr=bgcolor:#FFFFFF][td]cristinaschlundt@gmail.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]bradpalecek@edinarealty.com[/td][/tr]

[tr=bgcolor:#FFFFFF][td]rachelchampion82@gmail.com[/td][/tr]
[/table]

you can add more domains if necessary

 

[Leith Ross]

Hello larrylime,

I would say the problem is the emails are HTML and not pure text. That is why the Word document displayed them correctly. The list was most likely either an HTML Table, or Unordered List, either of which Excel can not display correctly after a Paste operation because there is no new line character at the end of the email. Seeing the Word document would have told me exactly what I needed to know and would have guided me in providing a solution.

 

[larrylime]

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

[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