Excel(?) query...

Discussion in 'General Chat' started by mr cat, Jan 16, 2006.

  1. mr cat

    mr cat Member of the month

    Joined:
    Jul 31, 2003
    Messages:
    3,375
    Likes Received:
    5
    got a quick query here...

    I've got a list of users e-mail addresses that include their CC mail, MS mail and smtp addresses etc in a single cell in excel - now, I only want a list of their smtp addresses - i.e. to remove the other e-mail addresses and only keep the smtp addresses...

    the smtp address starts and ends with a % sign - which should make things easier, but they aren't always within the same posotion in that cell either...

    so, just to clarify - each user populates a single cell (per row) that contains a list of different e-mail addresses in a single string and I only need the following -
    %SMTP:blah@blah%


    any ideas..?

    cheers
     
    mr cat, Jan 16, 2006
    #1
  2. mr cat

    Coda II getting there slowly

    Joined:
    Nov 17, 2004
    Messages:
    603
    Likes Received:
    1
    Location:
    Devon
    I know nothing about excel but have just downloaded a viewer for it and one of the things I came across whilst looking for it was an extractor to do (it sounds like) what you want, it's called email extractor and it's at: download.com

    Although reading again it may do the exact opposite of what you want... sorry!
     
    Coda II, Jan 16, 2006
    #2
  3. mr cat

    garyi Wish I had a Large Member

    Joined:
    Jun 19, 2003
    Messages:
    1,964
    Likes Received:
    0
    Can't you just hide the relevant columns?
     
    garyi, Jan 16, 2006
    #3
  4. mr cat

    RickyC

    Joined:
    Jan 11, 2004
    Messages:
    329
    Likes Received:
    0
    I thnk he said they are all in the same column.

    Don't know of any way to do that I'm afraid.

    Cheers

    Rich
     
    RickyC, Jan 16, 2006
    #4
  5. mr cat

    mr cat Member of the month

    Joined:
    Jul 31, 2003
    Messages:
    3,375
    Likes Received:
    5
    yeah, they're in the same colum - and to further confuse things the MS mail addresses are surrounded by the % sign and if the smtp address is the last one in the list then it doesn't have the % sign around it...
     
    mr cat, Jan 16, 2006
    #5
  6. mr cat

    mr cat Member of the month

    Joined:
    Jul 31, 2003
    Messages:
    3,375
    Likes Received:
    5
    sorted now...found this!

    =MID(D2,(FIND("SMTP",D2,1)+5),(FIND(".uk",D2,(FIND("SMTP", D2,1))+5)-(FIND("SMTP",D2,1)))-2)
     
    mr cat, Jan 16, 2006
    #6
  7. mr cat

    garyi Wish I had a Large Member

    Joined:
    Jun 19, 2003
    Messages:
    1,964
    Likes Received:
    0
    Wow!

    Would not an IF Command be easier. such as IF+% THEN etc

    As you can see I am not the most expert on this.
     
    garyi, Jan 16, 2006
    #7
  8. mr cat

    mr cat Member of the month

    Joined:
    Jul 31, 2003
    Messages:
    3,375
    Likes Received:
    5
    I'm no expert either - I got a tip from avform and a colleague amended the formula too...i didn't really have a clue...sigh!!
     
    mr cat, Jan 16, 2006
    #8
  9. mr cat

    Paul Ranson

    Joined:
    Sep 4, 2003
    Messages:
    1,602
    Likes Received:
    0
    Location:
    An octopus's garden.
    That '.uk' looks like it's lurking to bite sooner or later. Do it properly in VBA.

    Paul
     
    Paul Ranson, Jan 16, 2006
    #9
  10. mr cat

    mr cat Member of the month

    Joined:
    Jul 31, 2003
    Messages:
    3,375
    Likes Received:
    5
    you offering..??

    I haven't touched VBA since Uni... :rolleyes:
     
    mr cat, Jan 17, 2006
    #10
Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads

  1. tweeter seas Excel millenium t25-002 or vifa D27tg-46-06?

Loading...