Q. Is there a way to include both a formula and text in the same cell?
A. You can include both a formula and text in the same cell in Excel. You can do this in many ways, depending on what you are trying to accomplish. The first screenshot below shows an example of when it may be useful to combine a formula and text in the same cell.
The challenge here is to display a message in cell G31 that includes how many more technical hours a person needs for CPE. In this example, the required total is 40 hours. The total number of hours completed so far is a variable contained in cell E31. So, to display the remaining CPE hours as part of a text string in cell G31, you would type in the following ="You need " & 40-E31 & " more technical hours". With 23 entered into E31 in the example shown in the screenshot below, the message will read "You need 17 more technical hours." The message will display how many more technical hours are needed and will adjust based on the number in cell E31.
Here are some tips that may not be obvious just looking at the formula. First, you must start with an equal sign so Excel knows it is dealing with a formula. Otherwise, Excel will simply display exactly what you typed and not perform the calculation. Remember to include a space between the last letter and the end quote with "You need ". The space must be included to show up in cell E31.
I included several other spaces in the formula above, but only one of those has to be there. You must have the space between the beginning quote and the first letter in "more technical hours" for that space to be included in the message that appears in cell G31.
As for the other spaces, I included them to make it easier to see the different parts of the text string, the formula, and the "&" signs that connect two halves of the text with the formula. None of the spaces here are required. As you can see in the screenshot below, the formula works without these spaces.
You can access an Excel workbook and accompanying video below for combining a formula and text in the same cell.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to [email protected].We regret being unable to individually answer all submitted questions.
