Formulas in sentences in Excel and Calc
In this tutorial we will show you how to program simple sentences which contain formulas. You may use either Microsoft Excel
We will cover the process of inserting simple formulas that point to another cell, explore random sentence generation methods and word replacement within sentences.
Insert simple formula between text
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”:
Insert random word into 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.
The RANDBETWEEN function will provide a random cell number.
The 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:
INDEX(A1:C1;RANDBETWEEN(1;COUNTA(A1:C1)))
Here is how our sentence will look like with this formula:
="This is my "&INDEX(A1:C1;RANDBETWEEN(1;COUNTA(A1:C1)))&" sentence"
Replace multiple values in a 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 S1 and 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!
