If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
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)) |
Thread Tools | |
Display Modes | |
|
|