A lot of times, we need to work with text data in Excel. It could be Names, Address, Email ids, or other kinds of text strings. Often, there is a need to combine cells in Excel that contain the text data. Your data could be in adjacent cells (rows/columns), or it could be far off in the same worksheet or even a different worksheet.
How to Combine Cells in Excel
In this tutorial, you’ll learn how to Combine Cells in Excel in different scenarios:
How to Combine Cells without Space/Separator in Between.How to Combine Cells with Space/Separator in Between.How to Combine Cells with Line Breaks in Between.How to Combine Cells with Text and Numbers.
How to Combine Cells without Space/Separator
This is the easiest and probably the most used way to combine cells in Excel. For example, suppose you have a data set as shown below: You can easily combine cells in columns A and B to get a string such as A11, A12, and so on.. Here is how you can do this:
Enter the following formula in a cell where you want the combined string: =A2&B2 Copy-paste this in all the cells.
This will give you something as shown below: You can also do the same thing using the CONCATENATE function instead of using the ampersand (&). The below formula would give the same result:
How to Combine Cells with Space/Separator in Between
You can also combine cells and have a specified separator in between. It could be a space character, a comma, or any other separator. Suppose we have a dataset as shown below: Here are the steps to combine the first and the last name with a space character in between:
Enter the following formula in a cell: =A2&" “&B2 Copy-paste this in all the cells.
This would combine the first name and last name with a space character in between. If you want any other separator (such as comma, or dot), you can use that in the formula.
How to Combine Cells with Line Breaks in Between
Apart from separators, you can also add line breaks while you combine cells in Excel. Suppose you have a dataset as shown below: In the above example, different parts of the address are in different cells (Name, House #, Street, City, and Country). You can use the CONCATENATE function or the & (ampersand) to combine these cells. However, just by combining these cells would give you something as shown below: This is not in a good address format. You can try using the text wrap, but that wouldn’t work either. What is needed here is to have each element of the address on a separate line in the same cell. You can achieve that by using the CHAR(10) function along with the & sign. CHAR(10) is a line feed in Windows, which means that it forces anything after it to go to a new line. Use the below formula to get each cell’s content on a separate line within the same cell: This formula uses the CHAR(10) function in between each cell reference and inserts a line break after each cell. Once you have the result, apply wrap text in the cells that have the results and you’ll get something as shown below:
How to Combine Cells with Text and Numbers
You can also combine cells that contain different types of data. For example, you can combine cells that contain text and numbers. Let’s have a look at a simple example first. Suppose you have a dataset as shown below: The above data set has text data in one cell and a number is another cell. You can easily combine these two by using the below formula: Here I have used a dash as the separator. You can also add some text to it. So you can use the following formula to create a sentence: Here we have used a combination of cell reference and text to construct sentences. Now let’s take this example forward and see what happens when you try and use numbers with some formatting applied to it. Suppose you have a dataset as shown below, where we have sales values. Now let’s combine the cells in Column A and B to construct a sentence. Here the formula I’ll be using: Here is how the results look like: Do you see the problem here? Look closely at the format of the sales value in the result. You can see that the formatting of the sales value goes away and the result has the plain numeric value. This happens when we combine cells with numbers that have formatting applied to it. Here is how to fix this. Use the below formula: In the above formula, instead of using B2, we have used the TEXT function. TEXT function makes the number show up in the specified format and as text. In the above case, the format is $ ###,0.0. This format tells Excel to show the number with a dollar sign, a thousand-separator, and one decimal point. Similarly, you can use the Text function to show in any format allowed in Excel. Here is another example. There are names and date of birth, and if you try and combine these cells, you get something as shown below: You can see that Excel completely screws up the date format. The reason is that date and time are stored as numbers in Excel, and when you combine cells that have numbers, as shown above, it shows the number value but doesn’t use the original format. Here is the formula that will fix this: Again, here we have used the TEXT function and specified the format in which we want the Date of Birth to show up in the result. Here is how this date format works:
dd – Shows the day number of the date. (try using ddd and see what happens).mmm – shows the three-letter code for a month.yyy – shows the year number.
Cool… Isn’t it? Let me know your thoughts in the comments section.
CONCATENATE Excel Range (with and without separator).How to Merge Cells in Excel the Right Way.How to Combine Multiple Workbooks into One Excel Workbook.How to Combine Data from Multiple Workbooks into One Excel Table (using Power Query).How to Merge Cells in ExcelHow to Combine Duplicate Rows and Sum the Values in ExcelCombine Date and Time in Excel
what I’m looking for: 123 Happy Life #21 Los Angeles, CA 91111 =H2&CHAR(10)&I2&CHAR(10)&J2&”, “&K2&” “&L2
