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]

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,

This is my spreadsheet with powerquery enabled, i just need to know how to operate this tool to split those cells into new lines.

https://drive.google.com/file/d/13j4uIcx0bTBRfNkUkFlx57f6pXdAdNUr/view?usp=sharing

 

[sandy666]

I assume you want to split columns by comma into new rows, so
split by comma column C (in PowerQuery it has name header not column letter)
split by comma column E then split by comma column H
all info from other columns will be adapted automatically

1. PQ Editor:
2. Split column
3. select by delimiter
4. select comma

edit:
I can do nothing with your excel file because source of data is not accessible (source file is not attached/linked)

 

[renanpoco]

I assume you want to split columns by comma into new rows, so
split by comma column C (in PowerQuery it has name header not column letter)
split by comma column E then split by comma column H
all info from other columns will be adapted automatically

1. PQ Editor:
2. Split column
3. select by delimiter
4. select comma

edit:
I can do nothing with your excel file because source of data is not accessible (source file is not attached/linked)

Sandy i tried on column C and it worked but it didn't split the other columns at the same time so it generate duplicity from the data with comma on the other columns.
i also tried to select more than one column at the same time without success.

please check the link below its activated.

https://drive.google.com/file/d/13j4uIcx0bTBRfNkUkFlx57f6pXdAdNUr/view?usp=sharing

 

[renanpoco]

Sandy i tried on column C and it worked but it didn't split the other columns at the same time so it generate duplicity from the data with comma on the other columns.
i also tried to select more than one column at the same time without success.

please check the link below its activated.

https://drive.google.com/file/d/13j4uIcx0bTBRfNkUkFlx57f6pXdAdNUr/view?usp=sharing

i found a guy with a similar problem into MS Forum. It say SOLVED but i didn't understand the steps given.

https://community.powerbi.com/t5/De...le-columns-using-delimiter/m-p/438485#M202316

 

[sandy666]

I didn't say your file is wrong. I said I have no access to source data from another file: DataSource.Error: Could not find a part of the path 'C:\Users\Bruna\Desktop\Hebrew_lexicons_lvl1_2_3.xlsx'.
You post link to file without link to file with source data

I can try with your previous file as example

 

[renanpoco]

I did the split comma for all the 3 columns now im trying to find out how to remove the duplicates

 

[sandy666]

select all columns and Remove Duplicates (PowerQuery editor -Home - Remove Rows - ...)

 

[renanpoco]

select all columns and Remove Duplicates (PowerQuery editor -Home - Remove Rows - ...)

Cant remove without losing some data. This link below works for you ? i'm sorry for this cloud mess, i'm using my girlfriend laptop.

https://drive.google.com/file/d/13j4uIcx0bTBRfNkUkFlx57f6pXdAdNUr/view?usp=sharing

thanks

 

[sandy666]

No,
1 option:
copy all source data into shared file and post link to this file
2 option
post two links to the two files: source and result

btw. I said select ALL columns, not only three

 

[renanpoco]

No,
1 option:
copy all source data into shared file and post link to this file
2 option
post two links to the two files: source and result

btw. I said select ALL columns, not only three

Here is the source file with 1178 lines:
https://drive.google.com/file/d/1itrhe0IcQjFds48XiJq0fa04IwLa50tk/view?usp=sharing

and here is the result file with 35113 lines after split with powerquery:
https://drive.google.com/file/d/1qPoy6KktRwRqR-x_0VLicATw2AaSGNC_/view?usp=sharing