We will cover the process of inserting simple formulas that point to another cell, explore random sentence generation methods and word replacement within sentences.
In this example, we will create a sentence in OpenOffice Calc and insert one of the words as a reference from another cell. Here’s how to do it:
Step 1. Open a new Calc document and fill in some data. We typed “1st, 2nd and 3rd” into cells A1, B1 and C1.
Step 2. Write the sentence “This is my first sentence”.
Step 3. Make the sentence an executable formula by putting “=” in the beginning of the sentence and surrounding it with double quotes:
Step 3. Insert the data from cell A1 in the middle of the sentence. This is done by putting the formula (or cell number) inside double quotes and surrounding it with “&”:
="This is my "& A1 &" sentence"
The resulting sentence should be “This is my 1st sentence”:
In this example, we will insert a random word from a table range of different cells and insert it in the middle of the sentence.
For the purpose of choosing a value from a random cell we will use the
INDEX function which returns a value from a table of range.
RANDBETWEEN function will provide a random cell number.
COUNTA function is a helper function that will tell our RANDBETWEEN function how many cells we have to choose from.
Putting it all together we will have a complex function for random sentence generator in Excel:
Here is how our sentence will look like with this formula:
="This is my "&INDEX(A1:C1;RANDBETWEEN(1;COUNTA(A1:C1)))&" sentence"
This example will essentially build a template sentence in Excel. Templates are powerful features that let you use the same sentence and modify it automatically in many different ways.
Let’s create our template sentence first:
I am S1 and I like S2.
In this sentence
S2 are template arguments that we will replace with other random words.
Let’s create some possible words for S1: beautiful, kind, ugly, tall, green.
Words for S2: winter, snow, summer, to swim, frogs.
In order to replace S1 and S2 with one of our words, we will use the function
SUBSTITUTE and we will do it several times nesting multiple SUBSTITUTE functions into each other.
Step 1. Replace the word “S1” by using one SUBSTITUTE function:
=SUBSTITUTE(A1; "S1"; INDEX(E1:E5;RANDBETWEEN(1;COUNTA(E1:E5))))
The result for us was “I an green and I like S2”:
Step 2. Replace the word “S2” by nesting the previous code in one more SUBSTITUTE function:
=SUBSTITUTE(SUBSTITUTE(A1; "S1"; INDEX(E1:E5;RANDBETWEEN(1;COUNTA(E1:E5)))); "S2"; INDEX(F1:F5;RANDBETWEEN(1;COUNTA(F1:F5))))
Our resulting sentence was “I am ugly and I like snow”:
Excel is a powerful tool, but programming in a calc document may be difficult!