Excel question - randomly combining cells

Factual Questions
1

This is another one of those ‘I hope I can put it into words’ threads.

Probably pointless background: I make up languages. I like to have a list of randomly generated ‘words’ to chose from. Unfortunately the tool I used has disappeared and I can’t find a replacement. There’s a spreadsheet-like program that will do it, but it’s almost unusable (it crashes when saving and won’t open what it saved). Thus, I’m wondering if Excel can do it.

What I want is to be able to define a list of phonemes and a word format and have Excel generate from that. For example, on one sheet have

V C

a b
e d
i g
o p
u q

And somehow tell Excel to take random letters from those columns in the formats CVC VC CV and randomly generate some words.

I hope I’ve explained this right.

2

Not sure I have understood what you want but …

First set up your selection table something like below:
ColA ColB ColC
Num V C
0 a b
1 e d
2 i g
3 o p
4 u q

Second determine your random selection of the seven letters in range 0-4
letter #1 4 =INT(RAND()*5)
letter #2 3 =INT(RAND()*5)
letter #3 1
etc

Third create the triplet/couplets
CVC qod =VLOOKUP(B9,$A$2:$C$6,3,TRUE)&VLOOKUP(B10,$A$2:$C$6,2,TRUE)&VLOOKUP(B11,$A$2:$C$6,3,TRUE)
VC ab =VLOOKUP(B12,$A$2:$C$6,2,TRUE)&VLOOKUP(B13,$A$2:$C$6,3,TRUE)
CV ga =VLOOKUP(B14,$A$2:$c$6,2,TRUE)&VLOOKUP(B15,$A$2:$C$6,3,TRUE)

3

Awesome. That should do it. Thank you so much!