Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stripping characters (XP)

    What function could be used to strip certain characters from a text string in a cell.

    We're looking to strip extraneous information from email addresses that look like this:

    STMP:John.Doe@domain.com

    We want a function to be able to strip both the SMTP and the @domain.com and leave only the name in there.

    Anyone have any ideas how to accomplish this?

    --*Rob

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stripping characters (XP)

    Say that your text is in cell A1. I have assumed that you want to omit everything up to and including the colon : and everything from and including @.
    I'll use some intermediary formulas in cells; if you wish, you can create one formula to do the same.
    In cell B1, enter the formula =SEARCH(":",A1)
    In cell C1, enter the formula =SEARCH("@",A1)
    In cell D1, enter the formula =MID(A1,B1+1,C1-B1-1)
    If you have a column of such names, select B11 and fill down as far as necessary.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping characters (XP)

    For the SMTP piece, try something like:

    Cells.Replace What:="SMTP:", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

    For the "@domain.com" piece, you'll need to set up something using the InStr function - details are available in the Excel VBA Help.

    HTH
    Gre

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Stripping characters (XP)

    Hi Rob,

    This'll do it:
    =SUBSTITUTE(LEFT(A1,SEARCH("@",A1)-1),"STMP:","")
    where the data to be trimmed is in A1. Change the cell references to suit and you're in business!

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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