Randomly selecting cells with text criteria

Hi there,

Basically I have a 10 by 10 cell array. Individual cells contain three separate pieces of information separated by spaces, the first piece of which contains a three letter abbreviation (off which there are 4 different types) and the second and third are simply numbers corresponding to the cells position in x and y space respectively. So, for example a cell could read 'SPN 5 2' or 'PHL 5 4'

I basically wish to randomly select a cell from the array that must contain a specific three letter abbreviation (e.g. ‘SPN’)

I can randomly select a cell in the 10 by 10 array by using '=INDEX(array,RANDBETWEEN(1,10),RANDBETWEEN(1,10))', I just can't fathom how to select a cell with a specific abbreviation.

Any help would be greatly appreciated,

Cheers,

Hi Kaper,

Brilliant, thanks ever so much that works a charm! I should really get around to learning how to use UDF,

Cheers again, very much appreciated!

Brendan