pattern matching within text string

**nzdzy2** · 10-19-2011, 09:38 AM

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

**NBVC** · 10-19-2011, 09:46 AM

Try:

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

copied down

**nzdzy2** · 10-19-2011, 11:52 AM

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!!

**NBVC** · 10-19-2011, 12:02 PM

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.