How to add text to formula in excel
A free Office suite fully compatible with Microsoft Office
Formulas make life easy but adding text to these formulas even make life easier. You would be thinking how to add text to formula excel? Will it be easy or will I be able to do it on my own.
Yes, you can definitely do it and once you learn it, it becomes a piece of cake. Go through this step by step guide to add text to formula excel and you will be good to go.
Why it is significant to add text to formula excel?
Let me explain it through a simple example. If you are required to calculate the total number of unit sold you can use the summation formula, but it will be ambiguous. To make it more clear you can add text '2440 units sold'. First you need to quote this inside brackets and add the sign''
=A4&' sold '&B4'units.'
This is a snippet, we will take a closer step by step.
How to Add Text in a Formula using Ampersand '&'
Ampersand can be used to add text in a formula on excel. It can be used either in the start, mid or after the formula. It is up to you, where it is easy for you to use it. Let's take an example of a hypothetical persons' expenses over a period of 3 months.
The information added on WPS office Excel/spreadsheet is in following manner; the first column comprises of the first name of the person., the second column includes the last name and the next three columns depict the trio-monthly expenses of these persons. The sixth column is assigned to Total expenses.
Now let's see how text and formula are adjusted in the cells of sixth column.
First of all, you need to insert the formula in F4
The formula will be =Total Expenses of '&A4&B4&' is &'$'&SUM(C4,D4,E4)
The formula used here is given above. In this formula column 1 and 2 I.e., First name and last name are joined and are enclosed using ampersand symbol than the next 3 columns are added using the formula of SUM.
SUM(number1, [number 2]…)
So the formula now comprise of text as well as formula.
To get the result, click on the cell 'F4', then insert the formula in the fx bar and press enter. Immediately you will get the sum of all the expenses in a with a proper sentence structure.
Moving onto next step which is extension of the formula.
Next up is extend the formula to the last cell. In the following example, copy the formula to the cell 'F10'
How to Add text before a Formula using CONCAT Function?
You can add text to formula excel using another function of WPS spreadsheet and I.e., CONCAT function.
The formula to be used in CONCAT function is as following:
CONCAT (text1, text2, [text3], …)
All you need is to add the syntax CONCAT before the formula. Besides this, you can also use the syntax CONCATENATE. These keywords can be used alternatively as these got the same work to do I.e., addition of text to formula.
(Note: The CONCAT is short form of CONCATENATE that is usable as well)
Let's take an example for better comprehension of this function.
Now let's get into steps to see how the formula works.
Insert this formula in the cell number F4. The formula will be as follows;
So our formula that we are going to use for our given information will become;
=CONCAT('The Total Expenses of ',A4,B4,'is $',SUM(C4,D4,E4))
Next up again is extend the formula to the last cell. In the following example, copy the formula to the cell 'F10'
Have you learned about how to add text to formula excel? If you want to know more about Excel (2016/2019/mac/online) features, you can view and follow WPS Academy. WPS is a diverse regime that offers various functions for you to do your official as well as academic work.
Download WPS Office to edit your word documents, make databases on excel sheets, design presentations on PowerPoint without paying a single penny. Download Now! Save your time and enjoy hassle-free work experience.
Was this helpful?
- 1. How to compare two excel sheets and highlight differences
- 2. How to import external data in WPS Spreadsheet
- 3. How to use the VLOOKUP function across multiple sheets in WPS Office Excel?
- 4. How to expand cells to fit text in Excel
- 5. How to insert a check box in WPS Spreadsheet
- 6. How to change date format in a cell