Separate names and email id

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi, assuming your data is in column A, Copy this Array formula in B1, then Drag to the right 5 cells and drag it down your data set. This one is just for the emails:


Excel 2016 (Windows) 32 bit
ABCDEFGH
1Adad Cortes , Carlos Aguilar , Cesar Martin , Daniel Mendoza , Janicxio Jungoacortes@abcerp.comcaguilar@abc.comcmartin@abc.comdmendoza@abc.comjjungo@abc.com
Sheet1
Cell Formulas
RangeFormula
B1{=IF(COLUMNS($A$1:A1)<=SUMPRODUCT(--(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),ROW(INDIRECT("A1:A"&LEN($A1)))^0)="<")),INDEX(MID($A1,SMALL(IF(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),ROW(INDIRECT("A1:A"&LEN($A1)))^0)="<",ROW(INDIRECT("A1:A"&LEN($A1))),""),ROW(INDIRECT("A1:A"&SUMPRODUCT(--(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),ROW(INDIRECT("A1:A"&LEN($A1)))^0)="<")))))+1,SMALL(IF(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),ROW(INDIRECT("A1:A"&LEN($A1)))^0)=">",ROW(INDIRECT("A1:A"&LEN($A1))),""),ROW(INDIRECT("A1:A"&SUMPRODUCT(--(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),ROW(INDIRECT("A1:A"&LEN($A1)))^0)=">")))))-SMALL(IF(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),ROW(INDIRECT("A1:A"&LEN($A1)))^0)="<",ROW(INDIRECT("A1:A"&LEN($A1))),""),ROW(INDIRECT("A1:A"&SUMPRODUCT(--(MID($A1,ROW(INDIRECT("A1:A"&LEN($A1))),ROW(INDIRECT("A1:A"&LEN($A1)))^0)="<")))))-1),COLUMNS($A$1:A1)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.



Where do you want to extract the names to?

(can i get some expert opinions on this formula?)

[explanation, it's finding the "<" and then the ">" in the cell, then with the MID function, it takes the positions of the "<"s and extracts this amount of charachters: positions of ">" minus positions of "<"]
 
Last edited:
Upvote 0
For example, do you want the first name in the next column, the email next to it, then the second name in the next column and so on, or what?
 
Upvote 0
Right, How do you want your data to be set up? (the formula mentioned above will list the emails in the columns to the right)
 
Upvote 0
Again, Please specify in what cells each element of your example (Adad Cortes <acortes@abcerp.com>, Carlos Aguilar <caguilar@abc.com>, Cesar Martin <cmartin@abc.com>, Daniel Mendoza <dmendoza@abc.com>, Janicxio Jungo <jjungo@abc.com>). Where should Adad Cortes go? Where should acortes@abcerp.com go?
 
Upvote 0
Assuming you have Excel 2016 with the TEXTJOIN function, and no more than 10 names per cell (can be adapted for more) you could try these.

#VALUE!
 
Last edited:
Upvote 0
Or if you have Excel 2016, you can try this:

=REGEXREPLACE(A2;"( <.*?>)+";"")


=SUBSTITUTE(SUBSTITUTE(REGEXREPLACE("ß>"&A2;(">.*?<");", ");"ß, ";"");">";"")
 
Upvote 0
Or if you have Excel 2016, you can try this:

=REGEXREPLACE(..
István, I have seen you make similar comments suggestions a couple of times & your suggestions have never worked for me (#NAME? error).
I am using Excel 2016 (via Office 365) and there is no mention of such a function in it.
Could it be related to a particular language version?


Later: Since drafting the text above and before posting I have done a search of some other threads mentioning this function and I haven't seen any positive responses to your suggestions from OPs. However, I did come across this thread that you were involved in and it makes me wonder if perhaps you have this UDF (or your own similar version) and hence your suggestions are working for you, but not for others? Just a thought. I'm interested if you can shed any further light on the issue.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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