There are two ways to combine text in Excel:
- Use CONCATENATE function; or better yet
- use “&” connector which is a shortcut of using CONCATENATE facility
The result from both are same but second one is easier, shorter and widely used method. I will explain both methods with examples.
Literally word concatenate means “to link in series” and this is exactly what CONCATENATE function does. It links of combines the text in a series that you provide it with. That’s all!
Function itself has the following syntax:
Which in words mean:
=Link(this text, [this text], and so on…)
This not only allow me to make text strings with text input but also combining values within different cells.
Example 1: Cell value + Text input
Lets say you have some text in one cell and want to use that AND add something to it as well, like in this picture you have “Excel” in cell A2 but you want to add word Learn to it as well. To do this in cell B2 you will enter:
Or you can do it like this:
Note: Pay attention to space after the word Learn and secondly that I wrapped the added word in double quotes ” “.
Bonus: Adding text to multiple cells
Here is one example where the names are completed by adding Man to every cell super quickly.
Now you are Excel Man!
Example 2: Combine text in two cells
This is where CONCATENATE function or & connector shines. Instead of typing all yourself. You simply give the references and Excel does the needful.
Here is the list of names separated and we need to combine them quickly:
Here is the formula to be inserted in my example:
=A4&” “&B4 in cell C4 and drag the fill handle down
Notice the use of two connectors and to add space I used space within quotation marks.
To do the same with conventional CONCATENATE function I would have used this formula:
Notice there is no difference in the result but application is far easier if we use & connector approach.
This technique of concatenating text strings prove extremely helpful in making dashboards, custom chart titles and chart data labels.