+ Reply to Thread
Results 1 to 4 of 4

Extract word ending with specific text

  1. 04-29-2013, 07:19 PM #1
    s_anr
    s_anr is offline
    Registered User
    Join Date
    10-21-2009
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Extract word ending with specific text

    I'd like to extract the site in each sentence/ Each sentence is in a single cell. Site name can be anywhere and ends with .com. So the logic is to see where .com is and count backwards till the space is reached and extract the site name. I tried with Find/ Mid but it didn't work. Trying to get some help here


    A1 : best search engine is google.com but it depends
    A2 : Best news site is cnn.com but I like bbc too
    A3 : best company is microsoft.com and i don't like it
    A4 : have you heard about apple.com which makes iphones

    Result :

    googe.com
    cnn.com
    microsoft.com
    apple.com
    Reply With QuoteRegister To Reply
  2. 04-29-2013, 07:26 PM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Extract word ending with specific text

    Try this formula in B1 copied down

    =IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(".com",A1)+3)," ",REPT(" ",99)),99)),"")
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 04-29-2013, 07:37 PM #3
    s_anr
    s_anr is offline
    Registered User
    Join Date
    10-21-2009
    Location
    San Francisco, USA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Extract word ending with specific text

    This is perfect Thank you!
    Reply With QuoteRegister To Reply
  4. 04-29-2013, 09:33 PM #4
    Marcol
    Marcol is offline
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract word ending with specific text

    A bit late, but if you define a name like so
    Name:= StringSplit
    Refers to:=
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Then this CSE array in B2, Dragged down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This should return the first email address in the string regardless of the domain name.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
    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)

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