How to Combine/Merge Columns in Excel

In this tutorial, you will learn how to combine/merge columns in excel completely.

When processing data in excel, we sometimes need to combine data in multiple columns into one column. There are some formulas and features we can use to run this combination process.

Want to know what are those formulas and features and how to use them correctly to combine columns in excel? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

How to Combine/Merge Columns in Excel 1: Ampersand Symbol (&)

The first excel columns combination method that we will discuss is the one using the ampersand symbol (&).

The way to combine data in multiple columns in excel using the ampersand symbol is quite easy. When you write your formula, just place ampersand symbols between the cell coordinates of those columns like this.



You can combine as many columns as you like. Just remember to put the ampersand symbols between your columns cell coordinates.

Write the formula once for the top cells of your columns and copy it down. You will immediately get all the merging results of your columns data!

To better understand the concept, here is its implementation example in excel.



In the example, we want to combine code parts in different columns into codes in one column. To do that, as you can see, we write the code parts columns cell coordinates and put ampersand symbols between them.

How to Combine/Merge Columns in Excel 2: CONCATENATE

Besides using the ampersand symbols, you can also use the Excel CONCATENATE formula to combine/merge columns in excel. Just input the cell coordinates of the columns you want to combine into CONCATENATE and you are good to go.

Here is the CONCATENATE general writing form to combine columns in excel.



Don’t forget to place commas ( , ) between your columns cell coordinates in CONCATENATE so the formula can work correctly.

Here is the implementation example of CONCATENATE for the columns combination purpose.



Write and input your CONCATENATE correctly to combine your columns in excel fast!

How to Combine/Merge Columns in Excel 3: Flash Fill (Excel 2013 Onwards)

You can use this flash fill method only if you have excel version 2013 or newer.

Flash fill is a great feature that can help finish our data input process if it has a pattern. If you want to combine your columns data, you can also use flash fill to do it fast.



To use the feature for the combination process, just combine the first data of your columns manually first. Flash fill needs an input pattern example from you to run its function.



After you finish combining the first data, go to the Home tab, click the Fill dropdown button, and choose Flash Fill. You can also just press the Ctrl + E buttons to run the flash fill feature quickly.



Activating the flash fill will make it finish your columns data combination for you in a… flash!



If you somehow get wrong columns combination results from flash fill, just try to fix one of its results. Flash fill will fix its results according to the fixing pattern if it can recognize it.

How to Combine/Merge Columns in Excel 4: CONCAT (Excel 2019 Onwards)

One disadvantage if you use CONCATENATE to combine your columns is you cannot input a cell range in it. If you happen to need to combine many columns, then this disadvantage will become troublesome. You will need to input the columns’ cell coordinates one by one and that process may take a lot of time.

Excel seems to recognize this problem, however, and provides the CONCAT formula for us to use since excel 2019. CONCAT is quite a similar formula to CONCATENATE in terms of its writing and function. One crucial difference between them is you can input cell ranges if you combine data using CONCAT!

Because of that difference, you can write your formula like this when you use CONCAT to combine your columns.



Just input the row cell range of your columns where the data you want to combine from them is. Doing that will make you get your columns combination results immediately!

Here is the CONCAT implementation example if we use it to combine columns in excel.



You can see that we get our columns combination results by inputting the columns data rows to CONCAT. If we do that when using CONCATENATE, we will get #VALUE errors instead.



Therefore, if you need to combine columns in excel by using a formula and a cell range input, consider using CONCAT!

How to Combine/Merge Columns in Excel 5: TEXTJOIN (Excel 2019 Onwards)

What if we want to ignore empty cells and want to have delimiters also in our columns combination results? If you have excel version 2019 or newer, then you can use the TEXTJOIN formula to help you with that.

Here is the general writing form of TEXTJOIN to combine columns in excel.



When using TEXTJOIN, you need to input the delimiter, TRUE/FALSE, and the columns’ cells you want to combine. The TRUE/FALSE there determines whether you want to ignore empty cells/data when you combine your columns or not.

The delimiter you input will be in between each column data you combine by using TEXTJOIN. You can input something like a comma, space, dash, or any kind of delimiter you want.



Similar to CONCAT, you can also input your columns data row cell range to TEXTJOIN if you want. Input it at the end of your TEXTJOIN inputs. Separate all the inputs of columns cell ranges, cells, and data you want to combine using TEXTJOIN with commas.

To better understand the TEXTJOIN concept, here is its implementation example in excel.



Ignoring empty data when using TEXTJOIN to combine our columns will make us combine only the columns cells with data. If we don’t ignore it, then the result is as you can see in the second row there. The empty data is still in our columns combination result, with delimiters that separate it with other columns data.

You can also see in the example that we can input a cell range when we combine columns using TEXTJOIN. TEXTJOIN also accepts cell coordinates and direct data typing when it combines our columns data.

If you don’t want to use delimiters in your columns combination results, input blank data as your TEXTJOIN delimiter (“” or just input an empty cell).

How to Combine/Merge Columns in Excel 6: Notepad

If you have a notepad application (who don’t!), then you can use it to combine your columns in excel too.

Just open your notepad and copy the columns’ data you want to combine to there. In the notepad, you can combine your data much easier since there are no cell boundaries that separate them.

After you finish combining your columns data in your notepad, copy the combination results back to your excel. Put them in the cells where you want to display them. By doing that, you have done your columns combination process!

How to Put Delimiters When Combining Columns in Excel

If you want to place delimiters in between the column data you combine, you should use TEXTJOIN if you can. As we discussed previously, TEXTJOIN can place delimiters in your columns combination results easily.

If you cannot or you want to put delimiters only in some parts in the combination result, input the delimiters manually. Do the inputs in the places you want when you write the formula to combine your columns. You can type them directly or use cell coordinates if you put your delimiters in those cells.



To better understand the concept, here is the example where we put delimiters in our columns combination results in excel. We use TEXTJOIN, ampersand symbols, and CONCATENATE to combine the columns here.



In the example, we use different delimiters to combine each columns’ data row that we have. As you can see, we can get the columns combination results by using TEXTJOIN, ampersand symbols, and CONCATENATE.

It is easier to combine columns with delimiters by using TEXTJOIN as we only need to input our delimiter to TEXTJOIN. If we use ampersand symbols or CONCATENATE, we need to input the delimiters in every place where we want them.

How to Add Additional Text When Combining Columns in Excel

Input text manually in your formula writing when you want to add it to your columns combination results. There is no special formula to add additional text, though, like the TEXTJOIN one for delimiters.

Here is an example when we add additional text in our columns combination results in excel. We use ampersand symbols and CONCATENATE to do the combination process here.



In the example, we add additional text to our columns combination results in the front or at the back. To do that, we input the text in the appropriate place when we write our formula using ampersand symbols and CONCATENATE.

If you need them, don’t forget to add spaces in the place where you combine your text and columns combination results. As you can see in the example’s formula writings, we must add them manually too within our additional text.

How to Correctly Display Number Data When Combining Columns in Excel

The numbers in the columns you want to combine may have certain formatting for their display in their cells. They might display themselves as dates or currency numbers while their real form in excel is actually plain numbers.

If you directly input those numbers when you combine columns, you won’t get them in their current display format. They will become plain numbers instead in your columns’ combination results. If you need to preserve their display format in your columns combination results, you need to apply TEXT to those numbers.

TEXT is a formula that excel provides to change the formatting of our data. We can use it when we combine our columns so our numbers will display in the format that we prefer.

Here is the general writing form of TEXT to correctly display our numbers when combining columns in excel.



You need to input TEXT with your number as its input when you write the formula to combine your columns. Input the right display format into your TEXT so you can display your numbers correctly in your columns combination results.

For better understanding, here is the TEXT implementation example in columns combination formula writings in excel.



We use TEXT in the example so we can display our date, currency, and number correctly. We input the number format we want into TEXT to get that display in each columns’ combination result.

If we don’t use TEXT, then here is what will happen.



As you can see, our date, currency, and number turn into their basic value in our columns combination results there. That happens when we don’t combine our number data using TEXT.

Therefore, use TEXT whenever you need to display your number data in a certain way in your columns combination result!

How to Remove Formulas After Combining Columns n Excel

Use formulas to combine your columns and now you want to remove those formulas while preserving the combination results? You can do that by applying the excel paste values feature.

For the feature implementation, highlight the cell range containing your columns combination formulas first. Then, press the Ctrl + C (Command + C in Mac) buttons on your keyboard to activate the copy mode on them.



Then, go to the Home tab and click the Paste dropdown button there. Click Paste Values from the dropdown list that shows up.



Done! You have removed your columns combination formulas while preserving their results in the cell range you highlight!

How to Merge Cells in Columns

If what you mean by combining columns is merging their cells per row, then use the merge cells feature in excel.

To use it, highlight the first row cells of the columns you want to merge first. Then, go to the Home tab and click the Merge & Center button there.



You have merged the first row in your columns! Now, to merge all the rows, move your pointer to the bottom right of your cell cursor on the merged row. Move it until it becomes a plus sign like this.



After it becomes a plus sign, click and drag your pointer until the last row of your columns. Release the drag and you have finished merging the cells in your columns!

Exercise

After you have learned how to combine/merge columns in excel, now let’s do an exercise. This is so you can understand the lessons in this tutorial more practically.

Download the exercise file and do all the instructions below. Download the answer key file if you have done the exercise and want to check your answers.

Link to the exercise file:
Download here

Instructions

Use any method you want to combine the columns in the exercise file according to the instructions below. Put your answers in the appropriate gray-colored column according to the instruction number.

1. Combine the first name, middle name, and last name columns without delimiters!
2. Combine the first name, middle name, and last name columns with comma delimiters!
3. Combine the first name, middle name, and last name columns with space delimiters. Put the word “Full Name:” in front of the columns combination results!

Link to the answer key file:
Download here

Additional Note

You can also preserve your number format when combining columns in excel by converting it to text data type first. However, that will make you unable to calculate the number you convert.



Related tutorials you should learn: