+ Reply to Thread
Results 1 to 8 of 8

extracting email addresses from cells

  1. #1
    Registered User
    Join Date
    11-13-2006
    Posts
    21

    extracting email addresses from cells

    I have been given a list of customers in Excel format, where the Cell Comments functionality has been abused to store a myriad of additional information under the customer name, such as email addresses, alternative phone numbers, names of representatives, family members etc.

    I have been able to get the comments exported into their own column, but now I am trying to find a way of extracting email addresses into the neighbouring column. For example, if the cell contains "Rep: [email protected], Mob: 2188437" I would like "[email protected]" to appear in the next column.

    What would be the easiest way of achieving this?

    Thanks!

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    This formula should extract email addresses from a cell,
    =TRIM(MID(SUBSTITUTE(" "&$A1&" "," ",REPT(" ",40)), FIND(REPT("@",COLUMNS($A1:A1)),SUBSTITUTE(SUBSTITUTE(" "&$A1&" "," ",REPT(" ",40)),"@",REPT("@",COLUMNS($A1:A1)),COLUMNS($A1:A1)))-40,80))
    Drag the formula down

    also try Text to Columns
    Select the column,goto data=>text to columns select delimited, play around in there to separate the text
    Last edited by davesexcel; 12-18-2008 at 06:17 AM.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    with info in a1 in b1
    Please Login or Register  to view this content.
    if there are more in same cell draging it into c1,d1 will exctract 2nd or third address as well
    formula got from ozgrid
    http://www.ozgrid.com/forum/showthread.php?t=75866

  4. #4
    Registered User
    Join Date
    11-13-2006
    Posts
    21
    Wow, excellent, thanks for the fast responses!

    I've tried both, and they nearly work but both have the same issue for me, which is to do with my data. My data cells contain line breaks, which the formulas don't consider. For example, a cell might look like this:

    Email: [email protected]
    Rep: Adrian (212939230)

    When the formula is run, I end up with:

    [email protected]:

    as it seems to ignore the line break.

    How could I fix that?
    Last edited by gdi2k; 12-18-2008 at 06:30 AM. Reason: Fixed typo & email link oddity.

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    What Kind of line breaks, lets see a sample of the workbook, no need to post the entire workbook just enough to see what is going on.

  6. #6
    Registered User
    Join Date
    11-13-2006
    Posts
    21
    Here's an example cell (with a couple of bits blanked out).

    The same effect can be achieved by using ALT+Enter when entering text into a cell.
    Attached Images Attached Images

  7. #7
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474
    Hopefully it is A char(13), you will need to get rid of them first, then the other formula should work
    =SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),CHAR(10)," ")

  8. #8
    Registered User
    Join Date
    11-13-2006
    Posts
    21
    That works perfectly, thanks for your help, you guys are geniuses!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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