+ Reply to Thread
Results 1 to 5 of 5

pattern matching within text string

  1. #1
    Registered User
    Join Date
    10-10-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    pattern matching within text string

    Been looking all over and can't seem to find an answer. Can u help?

    I want to see if a specific pattern exists within a series of text codes. Codes are separated by a spaces.

    The pattern I'm looking for is: "O" "DO" "O". Any "O" found cannot part of another code (for example: "DO" "OS"). Has to be on its own.

    Here is a sample of the text codes (cells AM3:AM8):

    O I H D DO H O (meets the pattern)
    O I H D DO H OS (does not)
    D DO H DO H O (does not)
    O I H D DO H O H DO OS (meets)
    D DC O DO I DO H OS O (meets)
    D DO D DO D O D DO D H OS (does not)

    This formula finds ALL occurrences of the pattern - even when the "O" code is part of another code: =IF(ISNUMBER(SEARCH("O*DO*O", AM3)),"YES","")

    Any way to isolate the "O"s?

    Much appreciated.

    - NZDZY2
    Last edited by nzdzy2; 10-19-2011 at 11:54 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: pattern matching within text string

    Try:

    =IF(ISNUMBER(SEARCH(" O * DO * O "," "&AM3&" ")),"Yes","")

    copied down
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-10-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Smile Re: pattern matching within text string

    It works! You ROCK NBVC!! Thank you very much.

    For my own learning - how do the spaces before and after each character work? I would think it means to find the text string only if it's preceeded and followed by a space - but that's not the case. What's it telling excel?

    Thank you again!!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: pattern matching within text string

    Actually, it is as you think, but notice that I also add " " before and after the cell reference part (" "&AM3&" ")... so this "artificially" adds a space before and after the string in AM3 so that I can check the pattern with space before and after each set of text to check. The spaces before and after the asterisks also ensure that I am looking for a space between the sets and so "O" in "DO" doesn't get counted.

  5. #5
    Registered User
    Join Date
    10-10-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: pattern matching within text string

    XLNT - Thank U for teaching me a new thing!

+ 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