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

L

larrylime

New Member
Joined
Dec 7, 2015
Messages
11
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
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?
 
Upvote 0
Leith Ross said:
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?
Click to expand...

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]
 
Last edited:
Upvote 0
.
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.
 
Upvote 0
Logit said:
.
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.
Click to expand...

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

Hmmm ....
 
Upvote 0
.
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:

Code: 
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
 
Upvote 0
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="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
 
Upvote 0
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.

 
Upvote 0
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


sandy666 said:
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
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
SEPARATE LARGE COLUMN INTO 4 COLUMNS
2
Replies
11
Views
219
Peter_SSs
Peter_SSs
M
  • Question Question
Conditional formatting depending on different strings of text
Replies
8
Views
154
Peter_SSs
Peter_SSs
L
  • Solved
copy cell and cell above onto different sheet, under the next available line.
Replies
5
Views
208
Alex Blakenburg
A
S
  • Question Question
Retrieving data by dynamic address
Replies
1
Views
88
NdNoviceHlp
NdNoviceHlp
Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
841
felixstraube
F

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top