As a business owner or an analyst, you may have concatenate function in excel useful. Because it works to combine multiple columns in Excel into one cell. This process is known as concatenation. And it can save you a lot of time for merging columns. In this article about Concatenate Excel Columns, we will explore how to concatenate Excel columns for more efficient Excel usage.
Table of Contents
What is concatenate function in excel?
Concatenation is the process of combining two or more strings or values into one. In Excel, concatenation is performed using & operator or the CONCATENATE function. When you do this, you join the contents of two or more cells into a single one.
For example, here we suppose you have a dataset containing first names and last names in separate columns. You can use concatenation to combine these columns into one column of full name.
The Syntax of the Concatenate Excel Columns
The CONCATENATE function can really join two or more text strings into one. The syntax of the CONCATENATE function is as follows.
= CONCATENATE(text1, [text2], …)
In this syntax, text1 is the first text string that you want to merge. And [text2] is an optional argument representing the second text string to join. You can add up to 255 arguments to the CONCATENATE function.
how to concatenate two columns in excel by Using the “&” Operator
The & operator is a shortcut method of concatenation in Excel. To use the “&” operator to concatenate columns, you need to select the cell and type the following formula.
= A1 & B1
In this formula, A1 and B1 are the cells you want to concatenate. The “&” operator joins the contents of the two cells into one.
concatenate a column in excel
You can also use the “&” operator to add separators between the concatenated values. For example, if you want to add a comma and space between two columns, you can use the following formula:
= A1 & “, ” & B1
This formula will concatenate the contents of cells A1 and B1 with a comma and space between them.
concatenate in excel with Text and Numbers
If you want to concatenate columns containing both text and numbers, you should convert the numbers to text first. You can do this by using the TEXT function in Excel. Because this one converts a number to text in a specific format.
For example, we have two columns containing the first name and age of a group of people. You want to create a column that shows the person’s name and age in the format Name (Age).
what is concat in excel
= A1 & ” (” & TEXT(B1, “0”) & “)”
In this formula, A1 contains the person’s name. While the B1 contains their age. The TEXT function converts the age value in cell B1 to text in the format 0. And this means no decimal places.
Concatenating Columns with Line Breaks
If you want to concatenate columns with line breaks, you can use CHAR(10) function in Excel. This function normally represents a line break character in Excel.
In case you have two columns containing the address of a customer. And with the street address in one column and the city, state and zip code in another column. You want to create a column that shows the full address with a line break between each component. If you wanna do this, you need to use the following formula.
how to concatenate in excel
= A1 & CHAR(10) & B1
In this formula, A1 contains the street address. While B1 contains the city, state and zip code. The CHAR(10) function adds a line break between the two columns. So this is indeed resulting in a concatenated cell with full address.
Concatenating Columns with Conditional Formatting
Conditional formatting basically highlights specific cells based on certain conditions. You can use conditional formatting with concatenation to create a dynamic output. This means it changes based on the values you write.
In case you have two columns containing the first name and last name. Also here, you want to create a column that shows the full name in bold if the person is over 30 years old. You can use the following formula in the concatenated cell:
formula for concatenate
= A1 & ” ” & B1
Then, you should select the concatenated cell and go to the Home tab in the Excel ribbon. And you will click on Conditional Formatting and select New Rule. In the New Formatting Rule box, you can select Use a formula to determine which cells to format. Lastly, you will enter the following formula:
= B1 > 30
This formula checks if the value in cell B1 is greater than 30. The cell will be in bold text if the condition is true.
Tips and Tricks for Concatenating Excel Columns
- You can use the CONCATENATE function for more than two columns with easy “&” operator. But if you need to join more than two columns, it can become a bit messy. In such cases, you can use the CONCATENATE function.
- Now, you may try using the TEXT function to format numbers. If you need to concatenate cells in columns containing numbers, first, you convert them to text using the TEXT function. So the next formula will work correctly.
- Use the CHAR(10) function for line breaks. If you need to add line breaks between concatenated columns, use the CHAR(10) function to represent the line break character.
- You can use conditional formatting so the cells change in colors or format as you assign a formula.
Conclusion
Concatenating columns in Excel can save you a lot of time. Because you can combine two or more columns into a single cell with easy methods we mentioned. With these techniques, you can handle complex concatenation tasks.
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
