Formulas in sentences in Excel and Calc
By Freya, last updated September 15, 2019
In this tutorial we will show you how to program simple sentences which contain formulas. You may use either Microsoft Excel or OpenOffice Calc for this purpose.
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 “&”:
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 function which returns a value from a table of range.
The function will provide a random cell number.
The 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:
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:
In this sentence and 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 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:
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:
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!
You may use these HTML tags and attributes:
- Does chemotherapy leave men sterile permanently
- What is the perfect age to be
- Do you regret any of your regrets
- Are you allergic to crab
- Who is the greatest at archery
- How popular is football in Iceland
- Do surgeons operate when they are sick
- Can you upload tasks to Asana
- Does the Quran mention Shiva
- Does Balaji Viswanathan know Kannada 1
- Do you have unexplained fears
- Which airline company name ends with India
- Has science discovered God
- Could the plague return to Europe
- Why can t we resolve world hunger
- What does one do about chronic depression
- Is the MHT CET result percentile based
- What is the Great Black Wasp
- Why do you love your Tesla
- How do you play footsie