+ Reply to Thread
Results 1 to 4 of 4

Formula to find and return text string within a cell full of text strings

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    ann arbor
    MS-Off Ver
    Excel 2010
    Posts
    67

    Formula to find and return text string within a cell full of text strings

    Sorry for the confusing title, I will try my best to make this clearer. I have a sheet with cells like this:

    Cell A1: hat cat dog house www.url.com/page1 house kitten <monkey> www.url.com/pagexxxxxxx fish hotel

    Cell A2: office door window desk www.url.com/page2 mouse table <monkey> www.url.com/pageyyyyyyy dog chain

    What I need to do is have the first text string which starts with "www." in the cell is kept and all the rest deleted. It would look like this:

    Cell A1: www.url.com/page1

    Cell A2: www.url.com/page2

    Is there a formula I can run to do this or would that have to be a macro of some type?

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula to find and return text string within a cell full of text strings

    hi there. try this in B1:
    =LEFT(MID(A1,FIND("www.",A1),255),FIND(" ",MID(A1,FIND("www.",A1),255))-1)

    copy down. if required, copy the whole of column B & paste special -> values into column A. delete column B

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    11-13-2012
    Location
    ann arbor
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Formula to find and return text string within a cell full of text strings

    Thanks a ton! Say would there be any way to change the formula to return the second occurrence of "www."?

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula to find and return text string within a cell full of text strings

    glad to help. not sure if your real data is like that, but when i copied your eg over to Excel, i noticed the space after the 2nd url isn't a regular space. it's something called non-breaking space. so if you use:
    =LEFT(MID(A1,FIND("www.",A1,FIND("www.",A1)+1),255),FIND(" ",MID(A1,FIND("www.",A1,FIND("www.",A1)+1),255))-1)

    you'll be getting:
    www.url.com/pagexxxxxxx fish

    so, you can try using:
    =LEFT(MID(A1,FIND("www.",A1,FIND("www.",A1)+1),255),FIND(CHAR(160),MID(A1,FIND("www.",A1,FIND("www.",A1)+1),255))-1)

    hope that helps. thanks for the rep too. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find name(s) within text string that matches a list, then copy full name
    By juhaszp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2013, 08:48 AM
  2. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  3. Replies: 6
    Last Post: 02-28-2013, 11:27 AM
  4. Replies: 2
    Last Post: 09-24-2012, 03:11 PM
  5. Replies: 3
    Last Post: 05-25-2012, 05:45 PM

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