Remove e-mail address from general text field.

[InsomniacFolder]

Hello,

I have a column containing free text data dumped from a CRM program.
Each cell in the column contains an e-mail address at some point in it.

I want to strip out the entire e-mail address, and only the address, into
another column.

e.g.: "We tried using (e-mail address removed) but it didn't work" - would return
(e-mail address removed)
"His e-mail address (e-mail address removed) isn't valid" - would return
(e-mail address removed)
"If you use the (e-mail address removed) address be sure to copy in..." -
would return (e-mail address removed)

etc.

There is always a space before and after the e-mail address.

I can strip out the text from the @ to the right end of the e-mail address
using combinations of FIND, MID and LEN.

How, though do I find the left, front space preceding the e-mail address in
free text - in effect get MID to return a negative position from the @?

The space preceding the e-mail address is not always the same "n-th"
instance of a space in the cell.

Any suggestions greatfully appreciated.

Many thanks.
Regards,
KeLee

 

[InsomniacFolder]

Try this:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),
FIND("@",SUBSTITUTE(A1," ",REPT(" ",99)))-99,198))

That is excellent, and works exactly as intended, thank you so much.
KeLee

 

[Glenn]

Hello,

I have a column containing free text data dumped from a CRM program.
Each cell in the column contains an e-mail address at some point in it.

I want to strip out the entire e-mail address, and only the address, into
another column.

e.g.: "We tried using (e-mail address removed) but it didn't work" - would return
(e-mail address removed)
"His e-mail address (e-mail address removed) isn't valid" - would return
(e-mail address removed)
"If you use the (e-mail address removed) address be sure to copy in..." -
would return (e-mail address removed)

etc.

There is always a space before and after the e-mail address.

I can strip out the text from the @ to the right end of the e-mail address
using combinations of FIND, MID and LEN.

How, though do I find the left, front space preceding the e-mail address in
free text - in effect get MID to return a negative position from the @?

The space preceding the e-mail address is not always the same "n-th"
instance of a space in the cell.

Any suggestions greatfully appreciated.

Many thanks.
Regards,
KeLee

Try this:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),
FIND("@",SUBSTITUTE(A1," ",REPT(" ",99)))-99,198))