extracting from a cell ...

[markopolo123]

Dear Excel experts,

I will appreciate your help on the following:

I have a column of cells like the following:
johnsmith ([email protected])
I need a formula that takes away [email protected] and puts it in the side cell

thanks in advance for your time and know-how

Marcello
Barcelona

[shg]

Data > Text to columns, Delimited, Next, in the Other box put (, Finish.

Then replace ) with nothing in col B.

[martindwilson]

have you a few more examples?
but for that i'd use data text to columns delimited
use ( as delimiter. that would split it
johnsmith [email protected])
then find ) replace with nothing
lol it took me longer to type that than shg.

[shg]

... oh, and welcome to the forum.

[markopolo123]

thanks to you both guys, itīs been years (maybe more than 10) that I havenīt used excel formulas, so excuse me if I ask for the whole formula as to be written:

and yes, martindwilson, it could be like this

(letīs say they are in cells A1, A2, A3)

johnsmith ([email protected])
marystewart ([email protected])
marktwain ([email protected])

so, if you could please state the whole formula I should write in B1, B2, B3 to get [email protected] in B1, [email protected] in B2, [email protected] in B3

thanks once again ... Iīm desperate because I have 2.700 cells and doing it by hand itīs a nightmare

Marcello
Barcelona

[teylyn]

Hi,

go with shg's suggestion:

- select all cells with email addresses in the column. (make sure that the columns to the right are empty)
- click Data - Text to columns - tick "delimited" - click Next
- tick "other" and enter ( as the delimiter - click Finish
Now your email addresses are in their own column, all ending with a ")".
- Hit Ctrl-H to open the Find and replace dialog.
- Enter a ) in the "Find What" box and leave the "Replace With" box empty.
- Click Replace all.

Now the trailing ) is gone from all email addresses.

Alternatively, with your data in column A, starting in row 1, use this formula in B1 and copy down

=SUBSTITUTE(MID(A1,FIND("(",A1)+1,99),")","")

Then copy the result column and use Paste Special - Values to paste into another column. You can then delete column B.

[martindwilson]

or formula approach for both see attached

[markopolo123]

you are amazing, guys!

not only for the speed on answering back and for your expertise, but for helping for nothing in return, something very unusual these days ...

grateful regards,

Marcello
Barcelona