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!

 

[Michael M]

you didn't answer my question.....show us what you want the result to be from that cell that contains

bear (male), bear (female)

 

[Michael M]

Did you want this

Excel 2007
	A	B
1
2
3	bear (male), bear (female)	bear male
4		bear female
5
6
Sheet1

Cell Formulas
Range	Formula
B3	=LEFT(SUBSTITUTE(SUBSTITUTE(A3,"(",""),")",""),FIND(",",A3,1)-3)
B4	=MID(SUBSTITUTE(SUBSTITUTE(A3,"(",""),")",""),FIND(",",A3)-1,LEN(A3))

 

[renanpoco]

you didn't answer my question.....show us what you want the result to be from that cell that contains

Sure. This is what i have:
https://www.dropbox.com/s/ypb2r40hgyita96/Photo 04-11-18 02 35 23.png?dl=0

And this is what I expect do do:
https://www.dropbox.com/s/pv0xkxd8xyj3ia8/Photo 04-11-18 02 39 57.png?dl=0

Basically I need to create another line to accommodate the content after comma. It's possible ?

Thanks

 

[Michael M]

If you need to create new lines you will need a VBA solution, my manual formulas may be just as simple!!
See Post #12

 

[sandy666]

https://www.dropbox.com/s/ypb2r40hgyita96/Photo 04-11-18 02 35 23.png?dl=0

And this is what I expect do do:
https://www.dropbox.com/s/pv0xkxd8xyj3ia8/Photo 04-11-18 02 39 57.png?dl=0

Basically I need to create another line to accommodate the content after comma. It's possible ?

Use PowerQuery
Column Definition Split by comma to new rows

example excel file

 

[renanpoco]

Use PowerQuery
Column Definition Split by comma to new rows

example excel file

What is powerquery ?

This is the file in which I intend to do that:
https://www.dropbox.com/s/1egmr3seq0u5gov/memrise english copy.xlsx?dl=0

My Excel version is the 365 for iPad.

Thanks,

 

[sandy666]

Power Query aka Get&Transform : Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010
I don't know it will work for iPad so you need to check this.
I know your file and I post your file with the result so check post #15

Power Query - Get&Transform

If iPad is something like Mac - forget about PowerQuery

 

[renanpoco]

Power Query aka Get&Transform : Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010
I don't know it will work for iPad so you need to check this.
I know your file and I post your file with the result so check post #15

Power Query - Get&Transform

If iPad is something like Mac - forget about PowerQuery

I got your example file now cuz I'm still learning how to use this forum. I saw that you managed to do that at line 13/14, how do I apply this same query formula for the whole spreadsheet content ?

https://www.dropbox.com/s/qziwjqd14l6w423/Photo 04-11-18 03 33 26.png?dl=0

Thanks,

 

[sandy666]

First check if Excel for iPad support PowerQuery aka Get&Transform.
if not - look for vba solution
if yes - load source table into PQ editor then split each column by comma to the rows

some help for PowerQuery: Microsoft Power Query for Excel Help

 

[renanpoco]

First check if Excel for iPad support PowerQuery aka Get&Transform.
if not - look for vba solution
if yes - load source table into PQ editor then split each column by comma to the rows

some help for PowerQuery: Microsoft Power Query for Excel Help

Sandy,

I manage to find this powerquery on my laptop excel, could you tell me what to do to split into another cell the content detached by comma on columns C, E and H
maintaining all other column the same into these new lines?

Heres screenshot of the screen (the same you got in your example with green lines):
https://drive.google.com/file/d/1oqROR0WbrU3mM-M1m-QWEUyUVb6aFX2s/view?usp=sharing


Thanks vm,