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 but it didn't work" - would return

"His e-mail address isn't valid" - would return

"If you use the address be sure to copy in..." -
would return

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

"Glenn" wrote:

InsomniacFolder wrote:
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 but it didn't work" - would return

"His e-mail address isn't valid" - would return

"If you use the address be sure to copy in..." -
would return

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))


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

InsomniacFolder wrote:
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 but it didn't work" - would return

"His e-mail address isn't valid" - would return

"If you use the address be sure to copy in..." -
would return

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))