Quite often, data in an Excel spreadsheet is not ordered in the required way. Sometimes you need to combine texts kept in different cells to one cell. In today’s article, I will show you some methods for combining texts in Excel.
CONCATENATE function
The most popular method of combining strings in Excel is using CONCATENATE function. The formula syntax is:
=CONCATENATE(text1;[text2];…)
Text 1, Text 2,…, – arguments of the function, they define text or data strings which should be combined. Text 1,2…, can be defined as cell references such as A2, B5 etc., but also manually entered texts.
Example 1.
Below table contains the first and last names of some people in two separate columns: A and B. However, we want to have first and last name combined and entered into one cell. To do that, we will use the CONCATENATE function. Type in formula bar “=CONCATENATE(” and click on fx button to open new window allowing setting up function’s arguments:
Important note:
As you can see cells A2 and B2, which should be combined in this example, were added as function’s arguments in fields Text1 and Text3, but between them in Text2 there is additional argument ” “. This is space in quotation marks, because I want to separate first and last name with a space:
If I didn’t add this additional space between A2 and B2 my result will look as below (no space between first and last name):
Example 2.
Below table contains the first and last names of employees assigned to some projects. I want to receive in column C an information “Employee is assigned to Project…”. To do that, I will also use CONCATENATE function, however between arguments defining data to be combined I need to add manually text “assigned to” remembering to include spaces:
Combine text with & operator
To combine text/data strings together, you don’t always have to use special functions. Instead, you can use the special & operator, also called ampersand.
To use this method, you need to enter formula manually to formula bar: firstly type “=” sign, then enter cell references or type texts you want to combine and separate them by using & operator:
Important note:
Similarly to CONCATENATE function, if you want to get space between combined texts, you need to add space manually in quotation marks, as below:
CONCAT function
CONCAT function has been introduced to Excel 2019 and Office365 versions. It works in a very similar way as CONCATENATE, but CONCAT allows to combine data ranges, not only individual cells:
As you can see on the picture above, function’s argument is cell range B2:F2. To receive the same result by using CONCATENATE function, we will need to add each cell as separate argument:
TEXTJOIN function
Another function offered by Excel 2019 and Office 365 is TEXTJOIN. This function is very useful for situation, that we want to add any delimiter (e.g. comma, semicolon, dash, or space) between combined data. Syntax of this function is a little bit different than others presented earlier in this article:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Delimiter – this is the sign, which will be added between combined text strings/ranges;
Ignore_empty – it defines, if empty cells should be included in received result; if TRUE – empty cells will be ignored, if FALSE – empty cells will be not ignored;
Text1,2,…, – similarly to previous formulas these are text strings/cell references, which content will be combined.
Example 3.
In this example, we will try to get the same result as in example 1, so we will combine first and last names and add space between, however this time we will use TEXTJOIN function instead of CONCATENATE. To do that, type in formula bar “=TEXTJOIN(” and click on fx button to open new window allowing setting up function’s arguments:
Similarly to CONCAT function, in TEXTJOIN you can use cell ranges as argument, so in this example it will be A2:B2. Received result is as below:
To sum up, you can notice that the same result can be achieved by using CONCATENATE or TEXTJOIN functions, but also by & operator:
One thought on “How to combine text in Excel?”