+ Reply to Thread
Results 1 to 12 of 12

Removing all text except email address from one cell

  1. 01-09-2014, 05:59 AM #1
    Steve_123
    Steve_123 is offline
    Registered User
    Join Date
    04-30-2012
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    88

    Removing all text except email address from one cell

    Hi there

    I'd like to remove all text from within a cell that isn't part of an email address and copy it to the adjacent cell.

    For example, A1 might contain

    Please drop me an email at abc@123.com and I'll come back to you straight away.

    What I'd like is for A1 to just contain

    abc@123.com

    And B1 to contain

    Please drop me an email at and I'll come back to you straight away.

    Thanks as ever for the help

    Steve
    Reply With QuoteRegister To Reply
  2. 01-09-2014, 06:10 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Removing all text except email address from one cell

    In A1 Cell
    Please drop me an email at abc@123.com and I'll come back to you straight away.

    In B1 Cell

    =IFERROR(LEFT(MID(SUBSTITUTE(A1," ","~",LEN(LEFT(A1,FIND("@",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("@",A1))," ",""))),FIND("~",SUBSTITUTE(A1," ","~",LEN(LEFT(A1,FIND("@",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("@",A1))," ",""))))+1,5^5),FIND(" ",MID(SUBSTITUTE(A1," ","~",LEN(LEFT(A1,FIND("@",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("@",A1))," ",""))),FIND("~",SUBSTITUTE(A1," ","~",LEN(LEFT(A1,FIND("@",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("@",A1))," ",""))))+1,5^5))-1),"")

    In C1 Cell

    =SUBSTITUTE(A1,B1,"")

    Drag it down....


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 01-09-2014, 06:10 AM #3
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this ! 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 01-09-2014, 06:23 AM #4
    Steve_123
    Steve_123 is offline
    Registered User
    Join Date
    04-30-2012
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    88

    Re: Removing all text except email address from one cell

    Thanks both for your prompt responses, much appreciated! Sixthsense, that formula is great.

    Marc, with your solution it's worked almost perfectly but has left a word either side

    at*abc@123.com*and

    Do you know if there is a way to get rid of those? Also, how would I adapt the code to apply to all cells in column A, not just A1?

    Thanks again
    Reply With QuoteRegister To Reply
  5. 01-09-2014, 06:32 AM #5
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Removing all text except email address from one cell


    Works like a charm on my side with your original data ‼

    Please drop me an email at abc@123.com and I'll come back to you straight away.

    No such * !
    Reply With QuoteRegister To Reply
  6. 01-09-2014, 06:36 AM #6
    Steve_123
    Steve_123 is offline
    Registered User
    Join Date
    04-30-2012
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    88

    Re: Removing all text except email address from one cell

    Must have been user error, I tried again and it is working, thanks!

    Could you let me know how to do it so that it will do the same for all rows in Column A?

    Thanks again
    Reply With QuoteRegister To Reply
  7. 01-09-2014, 06:46 AM #7
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this ! 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 01-09-2014, 06:46 AM #8
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Removing all text except email address from one cell

    Slightly shorter formula version seems to work:

    =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",93)),FIND("@",SUBSTITUTE(A1," ",REPT(" ",93)))-93,186))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  9. 01-09-2014, 06:52 AM #9
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Removing all text except email address from one cell

    @ XOR LX,

    Nice combinations lol
    Reply With QuoteRegister To Reply
  10. 01-09-2014, 07:01 AM #10
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Removing all text except email address from one cell

    @Sixthsense

    Cheers!

    P.S. Like your (random) use of 5^5! Almost as arbitrary as my 93!
    Reply With QuoteRegister To Reply
  11. 01-09-2014, 07:06 AM #11
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Removing all text except email address from one cell

    @ XOR LX,

    Hm... I used Evaluate to understand your method
    Reply With QuoteRegister To Reply
  12. 01-09-2014, 07:07 AM #12
    Steve_123
    Steve_123 is offline
    Registered User
    Join Date
    04-30-2012
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    88

    Re: Removing all text except email address from one cell

    These are all great, thanks guys!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Removing numbers & non-alphabetic character from email address (before the @)
    By Rob8489 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-22-2013, 01:41 PM
  2. [SOLVED] Newbie in need of help. Need text from one cell to add to another- email to web address
    By Jmack29 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 08-24-2012, 09:19 AM
  3. Email selected print section to address located in a cell instead of email address in code
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2012, 01:57 PM
  4. Removing prefix on email address to leave company name in field
    By ecommerabmercer in forum Excel General
    Replies: 4
    Last Post: 12-07-2009, 12:54 PM
  5. Removing email address links
    By starguy in forum Excel General
    Replies: 7
    Last Post: 06-29-2006, 10:16 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1