+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Remove Unwanted Text from Cells

  1. #1
    Registered User
    Join Date
    06-23-2011
    Location
    Mexico
    MS-Off Ver
    Excel 2007
    Posts
    4

    Exclamation Remove Unwanted Text from Cells

    Good day everyone,
    I'll try to be as clear as possible with this question. I have a column with 500 rows and they contain different data but they all have an email address in them.
    i.e.
    row A1 : Hello my name is John and my email is john@mail.com thanks
    row A2 : Hi, my name is Sam and I have an email sam@sam.com good bye.
    row.....etc.

    what do I want to do? I want to get all email addresses from all rows, without all the rest of the text. I dont know how to do that, but I imagine it would be some sort of rule that detects the @ symbol, then selects the text before the @ until the first space, and then the text after the @ symbol until the first space, and deletes everything else in the cell.

    maybe Im just nuts and this is not possible but I thought I would ask the experts.

    thanks all for your help!

  2. #2
    Registered User
    Join Date
    05-15-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2007, Excel MAC 2008
    Posts
    63

    Re: Remove Unwanted Text from Cells

    Hi There
    You could use Text To Columns to separate all of your text into columns then leave the email address?.

    Data
    Text To Columns
    COnvert text to columns wizard
    Delimited
    Next
    Select space as the delimter
    Next
    finish

    this will leave you a column with your email addresses.

    Hope that helps

    Barbara

    http://www.howtoexcelatexcel.com

  3. #3
    Registered User
    Join Date
    06-23-2011
    Location
    Mexico
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Remove Unwanted Text from Cells

    Thank you for your prompt response Barbara.
    That option would work perfectly if all text was equal and I just needed to pull the emails out of the same column. Unfortunately, since all text for each cell varies, the email addresses are put in different columns, which difficults the extraction as the number of rows is bigger than 500.

    I'll appreciate any further input you are able to provide.

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Remove Unwanted Text from Cells

    rickygzz,

    Barbara Excel does bring up a method that will split the text strings into several different columns. However, because the text strings don't follow a set pattern and have differing amounts of words, the email addresses will end up in different columns. Also without something reliable for the =MID() formula to look for other than the @ symbol, I turned to creating a macro to extract the email addresses quickly and easily.

    Attached is a sample workbook that contains a button named "Get Emails" which is assigned to the following macro:
    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar

    EDIT: We posted at the same time, rickygzz
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-23-2011
    Location
    Mexico
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Remove Unwanted Text from Cells

    Thanks TigerAvatar,
    I will try to implement that solution and get back to post the results.


  6. #6
    Registered User
    Join Date
    06-23-2011
    Location
    Mexico
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Remove Unwanted Text from Cells

    Thank you TigerAvatar, this worked perfectly.

    have a nice day and weekend!

  7. #7
    Registered User
    Join Date
    08-01-2011
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Remove Unwanted Text from Cells

    Just want to say thanks so much for this!! I have been wrestling with the same problem for hours. I Googled it, wound up here and bingo! Your sheet/macro fixed it! Thanks again!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1