Use concatenation to combine text and numbers in Excel formulas
If you want to combine text with the results of a formula in a cell, you can use concatenation. Suppose you have calculated the total of a range of cells using a formula in cell D2. Now, you want to have cell A2 display the text "Today's sales are $12,000", where $12,000 is the value calculated in D2. As the value in D2 changes, you want the value in A2 to update automatically.
For example, suppose you have calculated the total of a range of cells using a formula in cell D2. Now, you want to have cell A2 display the text "Today's sales are $12,000", where $12,000 is the value calculated in D2. As the value in D2 changes, you want the value in A2 to update automatically.
To do this, type the following formula into A2:
="Today's sales are $"&D2
The secret here is to use the ampersand, &, to concatenate (or join) text and numbers together. The & symbol acts like a +, or plus sign, to join text strings together into one. A slightly more advanced version of the formula above might look like the following:
="You owe $"&D2&", which is due in 7 days"
You can also use the CONCATENATE() function to join multiple text strings together. The syntax of this function is as follows:
=CONCATENATE(text1,text2,...)
In this function, you can join up to 30 text strings together. To achieve the same result as the first example above, you could enter the following formula:
=CONCATENATE("Today's sales are $",d2)
.
Our Comment Policy.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.
I have some fruits apple, orange, pear, mango.... I want to add F1, F2, F3, F4... etc in front of the fruit name as prefix. Please tell how to do that using concatenate? Or is there any other formula for the same.