Need formula to remove text outside of parenthesis

[bighedstev]

I have emails copied to Excel that look like:

Email Owners Name (email address); Email 2 Owners name (email address 2); Email 3 Owners Name (email address 3); ect ect for about 500 email addresses total. When copied from the distribution list in Outlook and pasted into Excel, they're all in the same cell.

I need to extract only the email addresses and remove the names and parenthesis. Can anyone help with this? Mucho Gracias!

 

[renanpoco]

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD]מוכר, מוכרת[/TD]
[TD]salesperson ; seller[/TD]
[TD]moxer. moxeret[/TD]
[TD]noun[/TD]
[TD][/TD]
[TD]מוֹכֵר, מוֹכֶרֶת[/TD]
[/TR]
</tbody>[/TABLE]


three different delimiters: comma, semicolon, dot and comma again

There's some way to do that automatically for each column ? I tried with substitute but it search on the whole spreadsheet.

 

[sandy666]

IMHO easier and faster (less complicated) is manually work. Because you don't know where and what is on the right or wrong place.
You can't use SUBSTITUTE or REPLACE because in one place dot is correct but in another place is not, the same with semicolon, colon and comma

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]live (singular m.), live (singular f.)(insert comma)
live (plural, m.)(insert comma)
live (plural, f.)[/td][/tr]
[/table]

blue comma - correct
red comma - incorrect, should be changed to another character
(insert comma) - there should be comma

so as I said before, there is no any rule which you can use to correct whole data automatically
looking for a method of repair using a formula or something else will take you more time than doing it manually
but, of course, you can do what you want, it's up to you

edit.
btw. each row should contain the same number of delimiter(s), eg. comma
if not you will get inappropriate or blank values without assignment

 

[sandy666]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]correct[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]a,b,c[/td][td]x,y,z[/td][/tr]

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

[tr=bgcolor:#FFFFFF][td]a,b[/td][td]x,y,z,v[/td][/tr]
[/table]

... and the same number of parts after delimiter

---
maybe think about doing everything from the scratch properly

 

[renanpoco]

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]correct[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a,b,c[/TD]
[TD]x,y,z[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]incorrect[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]a,b[/TD]
[TD]x,y,z,v[/TD]
[/TR]
</tbody>[/TABLE]


... and the same number of parts after delimiter

---
maybe think about doing everything from the scratch properly

i found this website in which i was able to copy my entire old hebrew-portuguese course from memrise.
http://tech189.duckdns.org/memrise/

So now the only thing i do is translate all the lines from portuguese to english.
Do you know some function that could do that ? i know that under review tab there is translate option
but i can't find how to insert the translations on each line. Thanks


https://www.dropbox.com/s/48aby2tbcy2p3fa/hebrew-portuguese-course.xlsx?dl=0

 

[renanpoco]

i found this website in which i was able to copy my entire old hebrew-portuguese course from memrise.
http://tech189.duckdns.org/memrise/

So now the only thing i do is translate all the lines from portuguese to english.
Do you know some function that could do that ? i know that under review tab there is translate option
but i can't find how to insert the translations on each line. Thanks


https://www.dropbox.com/s/48aby2tbcy2p3fa/hebrew-portuguese-course.xlsx?dl=0

I did it on google spreadsheet using =GoogleTranslate(B5, "pt","en")

Thanks vm!

 

[hope7187]

If your data is in A1 try this in B1 =MID(A1,FIND("(",A1)+1,(FIND(")",A1)-FIND("(",A1))-1)

10 years later, this just solved a really annoying problem I had. Thank you!