Sometimes, you may want to use a value in one cell as part of the text in another cell. For example, you may have a number in cell A1 and you want to display it with some text in cell B1, such as “The number is 5”. To do this, you need to use a formula that can combine the value and the text together. There are two main ways to do this in Excel: using the concatenation operator (&) or using the TEXT function.
Concatenation operator (&)
The concatenation operator (&) is used to join two or more text strings together. You can use it to combine a value in one cell with some text in another cell. For example, the formula =A1&" is the number" will display the value in A1 followed by the text ” is the number” in the cell where you enter the formula. You can also use spaces and other characters within the quotation marks to format the text as you like. For example, the formula ="The number "&A1&" is odd" will display the text “The number ” followed by the value in A1 followed by the text ” is odd” in the cell where you enter the formula.
TEXT function
The TEXT function is used to convert a value to text in a specific format. You can use it to format a value in one cell with some text in another cell. For example, the formula =TEXT(A1,"0.00") will display the value in A1 as a text with two decimal places in the cell where you enter the formula. You can also use the TEXT function with the concatenation operator to combine a formatted value with some text. For example, the formula ="The number "&TEXT(A1,"0.00")&" is even" will display the text “The number ” followed by the value in A1 formatted with two decimal places followed by the text ” is even” in the cell where you enter the formula.
Procedures
To use a value in one cell as part of text in another cell in Excel formula, you can follow these steps:
- Select the cell where you want to display the combined text and value.
- Enter the formula using either the concatenation operator or the TEXT function, depending on how you want to format the value and the text.
- Press Enter to confirm the formula and see the result in the cell.
Example
Suppose you have a table like this:
| Product | Price | Quantity | Total |
|---|---|---|---|
| Apple | 1.5 | 10 | 15 |
| Banana | 2 | 5 | 10 |
| Orange | 3 | 3 | 9 |
You want to use the values in the Total column as part of the text in another column, such as “The total for Apple is 15”. To do this, you can use the following formula in cell E2:
=B2&"The total for "&A2&" is "&D2
This formula will concatenate the value in D2 with the text “The total for ” and the value in A2. The result will be:
| Product | Price | Quantity | Total | Text |
|---|---|---|---|---|
| Apple | 1.5 | 10 | 15 | The total for Apple is 15 |
You can copy the formula down to the other cells in column E to get the same result for the other products.
Alternatively, you can use the TEXT function to format the value in D2 with a currency symbol and a comma separator. For example, you can use the following formula in cell E2:
=B2&"The total for "&A2&" is "&TEXT(D2,"$#,##0.00")
This formula will convert the value in D2 to text with the format “$#,##0.00”, which means a dollar sign, a comma separator for thousands, and two decimal places. The result will be:
| Product | Price | Quantity | Total | Text |
|---|---|---|---|---|
| Apple | 1.5 | 10 | 15 | The total for Apple is $15.00 |
You can copy the formula down to the other cells in column E to get the same result for the other products.
Other approaches
Besides using the concatenation operator and the TEXT function, you can also use other functions to use a value in one cell as part of text in another cell in Excel formula. Some of these functions are:
- SUBSTITUTE: This function replaces a specific text within a text string with another text. You can use it to insert a value in one cell into a predefined text template in another cell. For example, you can use the following formula in cell E2:
=SUBSTITUTE("The total for [product] is [total]", "[product]", A2, "[total]", D2)
This formula will replace the placeholders [product] and [total] in the text template with the values in A2 and D2, respectively. The result will be the same as using the concatenation operator.
- REPLACE: This function replaces a specific part of a text string with another text. You can use it to insert a value in one cell into a specific position in another cell. For example, you can use the following formula in cell E2:
=REPLACE("The total for _____ is _____", 12, 5, A2, 22, 5, D2)
This formula will replace the five underscores starting from the 12th character and the 22nd character in the text string with the values in A2 and D2, respectively. The result will be the same as using the concatenation operator.
- INDIRECT: This function returns the value of a cell reference specified by a text string. You can use it to use a value in one cell as part of a cell reference in another cell. For example, you can use the following formula in cell E2:
=INDIRECT("The total for "&A2&" is "&"D"&ROW())
This formula will concatenate the value in A2 with the text “The total for ” and the cell reference “D” and the row number of the current cell. The result will be the same as using the concatenation operator.
