When entering data in spreadsheets, you may often find the need to combine text values such as names and addresses. For example, you could’ve received the first and last names of individuals, and your manager now wants you to create a list of their full names.
Manually jotting the information down in larger spreadsheets could take hours. However, Excel has different ways including using functions, operators, and built-in features for you to automate this, otherwise, tedious process.
Using Flash Fill
In our previous tutorial, we showed you how Flash Fill can be used to separate data from a single cell into different columns. But did you know that it can also do the opposite? That’s right – with Flash Fill, you can combine text from multiple columns into a single, unified cell.
For this, all you have to do is create a pattern. This step is very important because you want Excel to understand that you’re trying to combine texts. To create the pattern, you need to have the text you want to combine in the same column.
In this example, we’re combining the first and last names of employees at ABC inc. into their full names.
Notice how all first names are under column A and last names are under column B. In cell C2, I manually entered the full name. When I repeated this action in cell C3, Excel detected a pattern and automatically suggested filling in the remaining data. All I did was enter Tab and there were my data.
Even if you don’t get the suggestion to fill your data, you can head to the Data tab and select Flash Fill. It should get the job done for you.
Use the Ampersand Operator
When dealing with bigger sets of data, I mostly use the Ampersand operator while combining texts in Excel. This is because operators are easier to work with compared to working with functions.
Let’s use the same dataset we used before. To combine the text in cells A2 and B2, we used the Ampersand operator in the following formula:
=A2&B2
Merge Texts Using CONCAT and CONCATENATE
If you like using functions, you can either use the CONCAT or the CONCATENATE functions. The formula used to join texts is very simple using these functions. The CONCAT function is available for Windows and Mac that are running Office 2019 or Office 365. If you’re using an older version, you can opt for the CONCATENATE function.
If you’re looking to combine text values from cells A2 and B2, your formulas using the CONCAT and CONCATENATE functions should look something like this:
=CONCAT(A2,B2)
=CONCATENATE(A2,B2)
Combine Texts Using a Delimiter in Between
Although you can use delimiters in between other functions and the Ampersand function, the TEXTJOIN function has a dedicated argument to pass a delimiter. You could even choose to ignore or consider empty cells.
In the above example, if few employees had middle names, the entire data could be thrown off. This way, excel can add a blank space when there is no middle name. But this isn’t convenient.
Instead, you could use TEXTJOIN. This function ignores the adjacent empty cell instead of adding an extra space in between.
In this example, let’s combine the first, middle, and last names. If in case there is no middle name, we’ll be ignoring the empty cell. Check if your cell is truly empty, and enter the following formula in your spreadsheet:
=TEXTJOIN(“ ”, TRUE, A2, B2, C2)
Use Power Query to Merge Text Columns
You could automate the process by using Power Query. This tool can be used to manipulate your data, such as splitting and merging.
Although the name Power Query may be intimidating to new users, it is simpler to use than operators and functions. This is due to the lower likelihood of syntax errors.
Try using Power Query instead of using formulas if you have the data you wish to merge in two or more columns.
- Select your data range and head to the Data tab.
- From the Get & Transform section, select From table.
- In the Power Query editor, select the columns with the text you wish to combine. Select the first cell then hold Ctrl then click on the next column.
- Right-click on the column header and select Merge Columns.
- If you want to add a delimiter in between, select one under Separator.
- You can also name your new column in the window.
- Click OK.
- From the Home tab, select Close and Load.
