Separating multiple email addresses and names in Excel

russrokicki

New Member
Joined
Feb 18, 2014
Messages
4
Hi,

I need help with an Excel sheet. I have a list of users names and email addresses along with other data columns. Some of the Name and email cells contain multiple names and corresponing email address. I want to have these moved to their own row with all the other row/column data duplicated.

The names are unfortunately formatted like this: Anderson, Ashley C., Beard, James, Nextor, Ian

The email addresses are separated by a comma.

Is there a way to give each name and email address their own row and copy all the data from the original columns?

so original would have column a Name column B email column C Request Title D. date E. Status
the new rows would have all the same information but only one name per.

There are also many rows with only one name that I would not want to be affected.

Thanks in Advance!

Russell
 

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,448
Re: Need help separating multiple email addresses and names in Excel

Not quite sure how your data look like. If there are multiple names and emails in cells, how do name correspond to email?

Anyway, if you want to do it manually, you may want to look into Data/Text to Columns and set delimiter to comma. Copy name and email cells to a new sheet and play with it. You then copy across columns and paste/transpose to a single column. Depending on the data, it may take a long time. VBA may be the way to go.
 
Last edited:

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,042
Office Version
2019
Platform
Windows
Re: Need help separating multiple email addresses and names in Excel

Really need to see several rows of data shown in a manner that we can distinguish what you have so that we can provide a workable solution. What you have provided is not helpful.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,792
Office Version
365
Platform
Windows
Re: Need help separating multiple email addresses and names in Excel

I have assumed that ..
a) All names consist of two parts separated by a comma (& possibly a space). If some names contained a comma and some didn't (or contained more than one comma) then there would be no way to identify where one name ends and the next one starts
b) The number of email addresses in column B always matches the number of names in column A.

Given that, you could try this

Rich (BB code):
Sub Rearrange()
  Dim a As Variant, b As Variant, NameBits As Variant, EmailBits As Variant
  Dim c As Long, i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To uba2)
  For i = 1 To UBound(a)
    NameBits = Split(Replace(a(i, 1), ", ", ","), ",")
    EmailBits = Split(Replace(a(i, 2), ", ", ","), ",")
    For j = 0 To UBound(EmailBits)
      k = k + 1
      b(k, 1) = NameBits(2 * j) & ", " & NameBits(2 * j + 1)
      b(k, 2) = EmailBits(j)
      For c = 3 To uba2
        b(k, c) = a(i, c)
      Next c
    Next j
  Next i
  With Range("G2").Resize(k, uba2)
    .Value = b
    .Rows(0).Value = Range("A1").Resize(, uba2).Value
    .EntireColumn.AutoFit
  End With
End Sub

My sample data in columns A:E, code results in G:K

Excel Workbook
ABCDEFGHIJK
1NameEmailRequestDateStatusNameEmailRequestDateStatus
2Anderson, Ashley C., Beard, James, Nextor, Ianabc@def.com, ttt@abc.net, hhh_jjj123@ggg.com.ukReq 14/10/2019PendingAnderson, Ashley C.abc@def.comRe1 14/10/2019Pending
3Doe, JohnJohndoe@myplace.com.jpReq 25/10/2019CompleteBeard, Jamesttt@abc.netRe1 14/10/2019Pending
4Derf, Fred, Derf, FrederikaDerfFred@abc.def, DerfFrederika@abc.defReq 36/10/2019Not StartedNextor, Ianhhh_jjj123@ggg.com.ukRe1 14/10/2019Pending
5Doe, JohnJohndoe@myplace.com.jpRe1 25/10/2019Complete
6Derf, FredDerfFred@abc.defRe1 36/10/2019Not Started
7Derf, FrederikaDerfFrederika@abc.defRe1 36/10/2019Not Started
Sheet1
 

russrokicki

New Member
Joined
Feb 18, 2014
Messages
4
Re: Need help separating multiple email addresses and names in Excel

Thank you! This seems to do exactly what I wanted! I have to review all the new data but on first glance this is exactly what I wanted!

I have assumed that ..
a) All names consist of two parts separated by a comma (& possibly a space). If some names contained a comma and some didn't (or contained more than one comma) then there would be no way to identify where one name ends and the next one starts
b) The number of email addresses in column B always matches the number of names in column A.

Given that, you could try this

Rich (BB code):
Sub Rearrange()
  Dim a As Variant, b As Variant, NameBits As Variant, EmailBits As Variant
  Dim c As Long, i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A2:E" & Range("A" & Rows.Count).End(xlUp).Row).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To uba2)
  For i = 1 To UBound(a)
    NameBits = Split(Replace(a(i, 1), ", ", ","), ",")
    EmailBits = Split(Replace(a(i, 2), ", ", ","), ",")
    For j = 0 To UBound(EmailBits)
      k = k + 1
      b(k, 1) = NameBits(2 * j) & ", " & NameBits(2 * j + 1)
      b(k, 2) = EmailBits(j)
      For c = 3 To uba2
        b(k, c) = a(i, c)
      Next c
    Next j
  Next i
  With Range("G2").Resize(k, uba2)
    .Value = b
    .Rows(0).Value = Range("A1").Resize(, uba2).Value
    .EntireColumn.AutoFit
  End With
End Sub

My sample data in columns A:E, code results in G:K

Sheet1

ABCDEFGHIJK
1NameEmailRequestDateStatus NameEmailRequestDateStatus
2Anderson, Ashley C., Beard, James, Nextor, Ianabc@def.com, ttt@abc.net, hhh_jjj123@ggg.com.ukReq 14/10/2019Pending Anderson, Ashley C.abc@def.comRe1 14/10/2019Pending
3Doe, JohnJohndoe@myplace.com.jpReq 25/10/2019Complete Beard, Jamesttt@abc.netRe1 14/10/2019Pending
4Derf, Fred, Derf, FrederikaDerfFred@abc.def, DerfFrederika@abc.defReq 36/10/2019Not Started Nextor, Ianhhh_jjj123@ggg.com.ukRe1 14/10/2019Pending
5 Doe, JohnJohndoe@myplace.com.jpRe1 25/10/2019Complete
6 Derf, FredDerfFred@abc.defRe1 36/10/2019Not Started
7 Derf, FrederikaDerfFrederika@abc.defRe1 36/10/2019Not Started

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:307px;"><col style="width:332px;"><col style="width:67px;"><col style="width:76px;"><col style="width:85px;"><col style="width:16px;"><col style="width:142px;"><col style="width:188px;"><col style="width:74px;"><col style="width:84px;"><col style="width:99px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,792
Office Version
365
Platform
Windows
Re: Need help separating multiple email addresses and names in Excel

Thank you! This seems to do exactly what I wanted! I have to review all the new data but on first glance this is exactly what I wanted!
You're welcome. If any tweaks are required, post back with details.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only or just enough so readers know who you are addressing. :)
 

russrokicki

New Member
Joined
Feb 18, 2014
Messages
4
Re: Need help separating multiple email addresses and names in Excel

You're welcome. If any tweaks are required, post back with details.
Thanks again! I will be more careful with the quoting. I was hoping you could help tweak this a little for two purposes.

The first is if I have more than 5 columns of data with different information that i would want to retain. One sheet goes to Column L

The second tweak is I have another sheet that has duplicate emails but does not have multiple names or in some cases no name at all but i still want to separate the email address. The columns are similar otherwise and go to Column G (Name, Email, Request, Date, Status, Search Content)

Thanks again!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,792
Office Version
365
Platform
Windows
Re: Need help separating multiple email addresses and names in Excel

The first is if I have more than 5 columns of data with different information that i would want to retain. One sheet goes to Column L
This should cope with any number of columns provided that row 1 can be safely used to determine what the last column is. The only changed lines are highlighted

Rich (BB code):
Sub Rearrange_v2()
  Dim a As Variant, b As Variant, NameBits As Variant, EmailBits As Variant
  Dim c As Long, i As Long, j As Long, k As Long, uba2 As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, Cells(1, Columns.Count).End(xlToLeft).Column).Value
  uba2 = UBound(a, 2)
  ReDim b(1 To Rows.Count, 1 To uba2)
  For i = 1 To UBound(a)
    NameBits = Split(Replace(a(i, 1), ", ", ","), ",")
    EmailBits = Split(Replace(a(i, 2), ", ", ","), ",")
    For j = 0 To UBound(EmailBits)
      k = k + 1
      b(k, 1) = NameBits(2 * j) & ", " & NameBits(2 * j + 1)
      b(k, 2) = EmailBits(j)
      For c = 3 To uba2
        b(k, c) = a(i, c)
      Next c
    Next j
  Next i
  With Cells(2, uba2 + 2).Resize(k, uba2)
    .Value = b
    .Rows(0).Value = Range("A1").Resize(, uba2).Value
    .EntireColumn.AutoFit
  End With
End Sub


The second tweak is I have another sheet that has duplicate emails but does not have multiple names or in some cases no name at all but i still want to separate the email address.
If you don't have a one-to-one relationship between names and email addresses, I don't know what logic you would use to link them. With the example below, how would we know whether 2 emails belonged to John Doe and 1 to Fred Derf or vice-versa or even if all 3 emails belonged John and none to Fred (or vice-versa)?

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Doe, John, Derf, Fred</td><td style=";">email1,email2,email3</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br />


If you cannot be sure of such a 1-to-1 relationship, then even if there are say 2 names and 2 email addresses, it might be that both email addresses belong to the first person and none belong to the second person.
 

Forum statistics

Threads
1,078,358
Messages
5,339,738
Members
399,320
Latest member
zim1984

Some videos you may like

This Week's Hot Topics

Top