+ Reply to Thread
Results 1 to 4 of 4

loop to match if shorter strings appears in longer strings

  1. #1
    Forum Contributor
    Join Date
    04-23-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2013
    Posts
    116

    loop to match if shorter strings appears in longer strings

    I have 2 columns of data. Column A has words of length 11-15, and Column E has words of length 7-9. What I am trying to do is see if any of the words in column E appear in column A, and then list matches next to each other in columns F G H and onward.

    So, for instance, the word AIRPLANE may appear in column E and maybe PAPERAIRPLANE, AIRPLANEPILOT and MODELAIRPLANE appear somewhere in column A (there are no spaces between words and everything is capitalized). What I would like to do is have column F, G, H and I return this:

    F G H I
    AIRPLANE AIRPLANEPILOT MODELAIRPLANE PAPERAIRPLANE

    so my idea is something like:
    If A:A contains E1, cut A[n] and paste to F1 (where A[n] is the first cell that matches E1)
    repeat (pasting additional matches to G1 H1 etc.) until no more matches

    Then repeat everything for E2

    Thanks for the help!

  2. #2
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: loop to match if shorter strings appears in longer strings

    Here's some code that should do the trick. I have attached a file with a short example as well.

    INWORD.xlsm

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: loop to match if shorter strings appears in longer strings

    Backup your data.
    Copy code to a standard module (if you don't know how, ask)
    Run: MatchEwithA

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    04-23-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2013
    Posts
    116

    Re: loop to match if shorter strings appears in longer strings

    Thank you so much, both worked as I wanted.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1