Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove some text from a cell
I am attempting to update an email list. I have exported from Outlook all of
the emails that were returned to me because of a bad email address. Some of the address' are in a column along with a bunch of other words. My goal is to "pull" only the email address' out of that column. The only thing that I see that is consistent in each cell is the @ of the email address. Your help is greatly appreciated..... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove some text from a cell
Hi!
Try this: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1))) Seems to work on the following possibilities: please note that Joe<atMicrosoft.com is no longer Bob<atAol.com email is undeliverable if you can read this buffy<atnetscape.net Tom01<atcomcast.rr text text me<atyou.org Note: I've replaced the "at" sign so the examples won't hyperlink. Biff "JW" wrote in message ... I am attempting to update an email list. I have exported from Outlook all of the emails that were returned to me because of a bad email address. Some of the address' are in a column along with a bunch of other words. My goal is to "pull" only the email address' out of that column. The only thing that I see that is consistent in each cell is the @ of the email address. Your help is greatly appreciated..... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove some text from a cell
Well, I can see that line wrap is going to cause problems with formula so
here it is in chunks: =TRIM(RIGHT(SUBSTITUTE(LEFT (A1,FIND(" ",A1&" ",FIND("@",A1))-1) ," ",REPT(" ",LEN(A1))),LEN(A1))) Biff "Biff" wrote in message ... Hi! Try this: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1))) Seems to work on the following possibilities: please note that Joe<atMicrosoft.com is no longer Bob<atAol.com email is undeliverable if you can read this buffy<atnetscape.net Tom01<atcomcast.rr text text me<atyou.org Note: I've replaced the "at" sign so the examples won't hyperlink. Biff "JW" wrote in message ... I am attempting to update an email list. I have exported from Outlook all of the emails that were returned to me because of a bad email address. Some of the address' are in a column along with a bunch of other words. My goal is to "pull" only the email address' out of that column. The only thing that I see that is consistent in each cell is the @ of the email address. Your help is greatly appreciated..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I paste text data into ONE cell only? | Excel Discussion (Misc queries) | |||
remove text from cell containing numbers | Excel Discussion (Misc queries) | |||
Shade cell according to text? | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |