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!

 

[sandy666]

ok, I'll look at this a little later

btw. source file for your result file is: 'C:\Users\Bruna\Desktop\teste.xlsx'.
not source_lexicon.xlsx

Are these files teste.xlsx and source_lexicon.xlsx exactly the same?

 

[renanpoco]

ok, I'll look at this a little later

btw. source file for your result file is: 'C:\Users\Bruna\Desktop\teste.xlsx'.
not source_lexicon.xlsx

Are these files teste.xlsx and source_lexicon.xlsx exactly the same?

i just renamed the files to make easier to explain. Thanks

 

[sandy666]

don't do that after connect two files or if you do - change path to the source

 

[renanpoco]

ok, I'll look at this a little later

btw. source file for your result file is: 'C:\Users\Bruna\Desktop\teste.xlsx'.
not source_lexicon.xlsx

Are these files teste.xlsx and source_lexicon.xlsx exactly the same?

I renamed cuz i thought it would be easier to understand. Thanks

 

[sandy666]

example of source:
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]

3​

[/td][td]גר, גרה[/td][td]live (male, singular), live (female, singular)[/td][td]gar, gara[/td][td]verb[/td][td][/td][td]גָר, גָרָה[/td][/tr]
[/table]

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

1​

[/td][td]

4​

[/td][td]תלמיד, תלמידה, תלמידים, תלמידות[/td][td]student (m., singular)
student (f., singular)
students (m., plural)
student (f., plural)[/td][td]talmid, talmida, talmidim, talmidot[/td][td]noun[/td][td][/td][td]תַלְמִיד, תַלְמִידָה, תַלְמִידִים, תַלְמִידוֹת[/td][/tr]
[/table]

too much commas or no commas or commas are not in right places
your data is inconsistent

 

[renanpoco]

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

1​

[/TD]
[TD]

3​

[/TD]
[TD]גר, גרה[/TD]
[TD]live (male, singular), live (female, singular)[/TD]
[TD]gar, gara[/TD]
[TD]verb[/TD]
[TD][/TD]
[TD]גָר, גָרָה[/TD]
[/TR]
</tbody>[/TABLE]


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

1​

[/TD]
[TD]

4​

[/TD]
[TD]תלמיד, תלמידה, תלמידים, תלמידות[/TD]
[TD]student (m., singular)
student (f., singular)
students (m., plural)
student (f., plural)[/TD]
[TD]talmid, talmida, talmidim, talmidot[/TD]
[TD]noun[/TD]
[TD][/TD]
[TD]תַלְמִיד, תַלְמִידָה, תַלְמִידִים, תַלְמִידוֹת[/TD]
[/TR]
</tbody>[/TABLE]


too much commas or no commas or commas are not in right places

Do you have some idea about how to work this out or the only way is doing everything by hand ? Maybe there is some way to isolate the minority of cases that may need manual correction ? Thanks

 

[sandy666]

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

1​

[/td][td]

5​

[/td][td]חבר, חברה, חברים, חברות[/td][td]friend (m.singular)
friend (f.singular)
friends (m.plural)
friends (f.plural)[/td][td]xaver, xavera, xaverim, xaverot[/td][td]noun[/td][td][/td][td]חָבֵר, חֲבֵרָה, חֲבֵרִים, חֲבֵרוֹת[/td][/tr]

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

1​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]גר, גרה, גרים, גרות[/td][td=bgcolor:#E2EFDA]live (singular m.), live (singular f.)
live (plural, m.)
live (plural, f.)[/td][td=bgcolor:#E2EFDA]gar, gara, garim, garot[/td][td=bgcolor:#E2EFDA]verb[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]גָר, גָרָה, גָרִים, גָרוֹת[/td][/tr]
[/table]

look at this table and previous tables
your commas are random so hard to find any rule where it should be or not to be

IMHO you've a hard manual work now

 

[renanpoco]

[TABLE="class: head"]
<tbody>[TR]
[TD]

1​

[/TD]
[TD]

5​

[/TD]
[TD]חבר, חברה, חברים, חברות[/TD]
[TD]friend (m.singular)
friend (f.singular)
friends (m.plural)
friends (f.plural)[/TD]
[TD]xaver, xavera, xaverim, xaverot[/TD]
[TD]noun[/TD]
[TD][/TD]
[TD]חָבֵר, חֲבֵרָה, חֲבֵרִים, חֲבֵרוֹת[/TD]
[/TR]
[TR]
[TD="bgcolor: #E2EFDA"]

1​

[/TD]
[TD="bgcolor: #E2EFDA"]

5​

[/TD]
[TD="bgcolor: #E2EFDA"]גר, גרה, גרים, גרות[/TD]
[TD="bgcolor: #E2EFDA"]live (singular m.), live (singular f.)
live (plural, m.)
live (plural, f.)[/TD]
[TD="bgcolor: #E2EFDA"]gar, gara, garim, garot[/TD]
[TD="bgcolor: #E2EFDA"]verb[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"]גָר, גָרָה, גָרִים, גָרוֹת[/TD]
[/TR]
</tbody>[/TABLE]


look at this table and previous tables
your commas are random so hard to find any rule where it should be or not to be

IMHO you've a hard manual work now

Thank you vm for you help. I bulk uploaded It all into memrise and now i gonna start to refine it by hand.
https://www.memrise.com/course/2113613/eteacher-rosen-modern-hebrew-1-2-3/

 

[sandy666]

you can try another delimiter eg. semicolon ( ; ), if it doesn't exist in whole table

edit:

but I see there is many semicolons and colons, so you need to find character which doesn't exist in your source data and use it as delimiter or change all inappropriate commas to another character.

 

[sandy666]

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

2​

[/td][td]

4​

[/td][td]מוכר, מוכרת[/td][td]salesperson ; seller [/td][td]moxer. moxeret[/td][td]noun[/td][td][/td][td]מוֹכֵר, מוֹכֶרֶת[/td][/tr]
[/table]

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