For example, if you pull a list of names, these may be split into a column of first names and a column of surnames, when you really want to have the whole name in one cell. The good news is that it’s quick and easy to take the contents of two or more columns and put them together. Here’s how to combine two columns in Excel.
How to Combine Columns in Excel Using Ampersands
If you want to combine data from different columns, one of the easiest ways to do so is by using the ampersand (&) symbol. This symbol allows you to create a formula referencing multiple cells that combines the data from each of them. You can also use ampersands to add additional text or empty spaces. Once you’ve created your formula for one cell, you can quickly apply that formula to an entire column. To combine columns in Excel using ampersands:
How to Combine Columns in Excel Using CONCAT
Another method of achieving the same result is to use the CONCAT function in Excel. This is an updated version of the original CONCATENATE function. While the CONCATENATE function is still currently working, it is being deprecated, so it’s best to use the newer CONCAT function instead. To combine columns in Excel using CONCAT:
How to Create Text By Combining Columns in Excel
The above methods show you how to take data from two cells and put them together in a different cell. However, you can use the same methods to create a brand-new piece of text that includes the data from your selected cells. This can be useful for generating phrases that include the data from your columns. The method below uses ampersands, but you can achieve the same effect using the CONCAT function. To create text by combining columns in Excel:
How to Format Dates When Combining Columns
One issue that can occur when combining columns in Excel is when one of the columns you’re combining contains dates. By default, these dates lose their formatting and get converted into numbers. In order to display the dates correctly, we have to use the TEXT function to tell Excel how we want those dates to be displayed. To format dates when combining columns in Excel:
Analyzing Your Data in Microsoft Excel
Learning how to combine two columns in Excel allows you to create cells containing exactly the data that you want. This can be useful if you’re pulling data from somewhere else that comes in separate columns, but you would prefer to have it within the same cell. Excel is such a powerful application that many of us only scratch the surface of what it can do. For example, you can learn how to capitalize text in Excel if you want to use name data that’s currently all lowercase. You can use Excel to calculate someone’s age from their date of birth, or even use the PPMT function to calculate how much of your loan repayments are going towards paying off the principal. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
