+ Reply to Thread
Results 1 to 6 of 6

extract text string when you only know the last three letters of that string

  1. #1
    Registered User
    Join Date
    10-12-2012
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    2

    extract text string when you only know the last three letters of that string

    Hi,

    I am trying to extract all the words that end in -ing from data that looks like this:

    Cell A1: more precise. We are turning tables into smart surfaces that recognize our equipment and allow for multi-touch handling of images etc. We create siftables, that can be whatever we want them to be. And

    Cell A2: doesn’t pay real money, but it can lead to paid work. I am attending the second edition of Un-convention Groningen , which takes place in the shadows of the Eurosonic Noorderslag Seminar. As was the case with the first edition, i

    Cell A3: it is awesome. And I am not saying that just because the company I co-founded in 2009, organizes it :-D There is no money in arts, anymore. Obviously, a lot of the folks on the various panels

    Cell A4: If you have not encountered any of the products that 37Signals have given to the world, you are missing out. They created beautiful, elegant software that helps you manage projects, manage your client contacts, etc. What makes their products so great, is that they have always managed to resist the driv

    Cell A5: his town runs smoothly and he isn't very good at his job. People have noticed that the moon has become larger lately and although it defies belief, it has concerned them enough to the point that they are urging the mayor to evacuate the town out of precaution. Reason enough for non-believers to claim that no right-minded people ever thinks that the moon might be falling. Their fierce and vociferous argument

    So in other words, I am trying to extract 'turning' from A1, 'attending' from A2, 'saying' from A3, 'missing' from A4, 'urging' from A5, and to put these words in adjacent cells. Does anyone know of a formula that can do this?

    Many thanks!

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: extract text string when you only know the last three letters of that string

    There are multiple words ending in "ing" in some of the cells...

  3. #3
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: extract text string when you only know the last three letters of that string

    =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),FIND("ing",SUBSTITUTE(A1," ",REPT(" ",100)))-100,103))

    wil find the first ing word in A1
    Regards,
    Vandan

  4. #4
    Registered User
    Join Date
    10-12-2012
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: extract text string when you only know the last three letters of that string

    fantastic! much appreciated :-)

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: extract text string when you only know the last three letters of that string

    This will pull out the first "ing" word it finds:

    =TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A2, "ing ", "ing" &REPT(" ",1000)),1000))," ", REPT(" ", 100)),100))



    EDIT: Late to the party, hat-tip to Vandan.
    Last edited by JBeaucaire; 10-12-2012 at 01:20 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: extract text string when you only know the last three letters of that string

    I have marked this thread solved for you.
    In the future please select Thread Tools from the menu above and mark the 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)

Tags for this Thread

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