Remove e-mail address from general text field.

I

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 [email protected] but it didn't work" - would return
[email protected]
"His e-mail address [email protected] isn't valid" - would return
[email protected]
"If you use the [email protected] address be sure to copy in..." -
would return [email protected]

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
 
I

InsomniacFolder

Glenn said:
Try this:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),
FIND("@",SUBSTITUTE(A1," ",REPT(" ",99)))-99,198))
Click to expand...

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

Glenn

InsomniacFolder said:
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 [email protected] but it didn't work" - would return
[email protected]
"His e-mail address [email protected] isn't valid" - would return
[email protected]
"If you use the [email protected] address be sure to copy in..." -
would return [email protected]

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
Click to expand...


Try this:

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