Distance between 2 words in a cell that contains a block of text

**chandu1382** · 03-05-2013, 12:00 AM

Hi,

I have 10,000 cells each cell containing lots of paragraphs.
I need to match a pattern from each of these cells like:

if distance between word1 and word2 <= 3, then "yes"/"1"/"true" else "no"/"0"/"false"
it'll be really helpful if the code can count the number of such matches in each cell. (there can be more than 1 such match)

for example: if cell A1 contains: "the janitor cleaned the closet and started leaving home just when he heard a deafening sound. running towards the closet, the janitor slipped...."
if word1 and word2 are "janitor" and "closet"
B1: should show me 2. (or atleast "true")

search and find are counting all the characters but not words.
may be counting the number of spaces in between word1 and word2 would do.
please help.

Thanks.

**Leith Ross** · 03-05-2013, 01:08 AM

Hello chandu1382,

Welcome to the Forum!

When creating solutions for pattern matches, less is not more. You should post a copy of the workbook. This will provide a complete set of examples from which solutions can be developed and tested. In the long run, it will save you time.

To Post Your Workbook

At the bottom right of the Reply window, Click the button Go Advanced
At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
Click the Select Files button at the bottom middle of the dialog.
In the new window Find the file you want to upload, select it, and Click Open.
You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
Wait until the file has completely uploaded before you exit the File Manager dialog.