Posted to microsoft.public.excel.worksheet.functions
|
|
Remove e-mail address from general text field.
"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
|