Hi LEN(A1)-LEN(SUBSTITUTE(A1,"@","")) This part of the formula tells us how many "@" there are in the string. It compares the length of the original A1 with the length of a modified A1 where the all "@" are removed. In the case of your example, it's 2. We then substitute the 2nd instance of "@" with "~~". I chose "~~" because it's very unlikely to appear in your data, but you could choose any unlikely combination of characters. Then we use search to find the position of the "~~" We then feed that number to the LEFT function as applied to A1, but have to subtract 1 to prevent the inclusion of the "@" Please let me know if you need the explanation broken down any more. Regards - Dave.
From: anil.bha...@tatacommunications.com To: excel-macros@googlegroups.com Date: Fri, 18 Mar 2011 16:05:23 +0530 Subject: RE: $$Excel-Macros$$ Remove text from a string Amazing Dave… very well answered… Could you please let us know how “~~” this is useful. Could you please explain or give me a link to understand this. Regards, Anil Bhange IP Phone - 512320 | Mobile - 90290 32123 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Dave Bonallack Sent: Friday, March 18, 2011 3:28 PM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Remove text from a string Hi, If your text is in A1, try this in B1: =LEFT(A1,SEARCH("~~",SUBSTITUTE(A1,"@","~~",LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))))-1) Regards - Dave Date: Fri, 18 Mar 2011 13:46:12 +0530 Subject: $$Excel-Macros$$ Remove text from a string From: deepakexce...@gmail.com To: excel-macros@googlegroups.com Hi Masters, My query is to remove all the characters after the last "@" from the text. Example: The Raw test is deepak@gmail.comkaml...@yahoo.com The final text i want is deepak@gmail.comkamlesh Regards, Deepak Rawat -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
