Finding nth word in a range of cells containing text

Hi there,

I'm looking for a macro which would be able to find the nth word in a range of cells containing some text and for the cell containing that word to be selected.

e.g. in this sheet:

excel eg.png

is it possible to use a macro to find the 20th word (counting row by row i.e. A1, B1, C1, A2, B2 etc.), and then select/highlight the cell which contains the 20th word?

Thanks in advance

Put this code into a standard code module, and use it like this for a worksheet function:

=NthWord(A1:C7,20)

or like this in VBA

strW = NthWord(Range("A1:C7"),20)

Please Login or Register  to view this content.

To select the cell, you cannot use the function version, but this will do that:

Please Login or Register  to view this content.