Remove e-mail address from general text field.
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 |
Remove e-mail address from general text field.
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)) |
All times are GMT +1. The time now is 12:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com