Merging cells can help you organize data across several columns or rows. Sometimes, it can extract and combine the information from separate cells to place them into another row or column. That’s very useful, right? So keep reading because here you will learn how to do it!
This is a common aid people frequently use, maybe because it’s easy to find in the Home tab. You only have to follow the steps below:
Note: You must pick a range of continuous cells; you can’t merge separated ones.
Now that you’ve created a single cell, whatever you type into it will be aligned at the center, but don’t worry. You can change the settings later.
Despite the above, and Before merging the cells, if you have data in each cell you want to join, Excel will show you an error message saying: “Merging cells only keeps the upper-left value and discards other values.” This means you must be careful because Excel will merge the information but delete all you have on them, excluding the first and leftmost content.
Even though the “Merge and center” command is easy to use, the “Center Across Selection” is an excellent alternative to avoid some limitations you might find with the first technique, like sorting data and highlighting the cells without inconvenience.
In contrast with the first method, this command is not as simple to find in your worksheet, but don’t worry, to access it, you need to do the following:
Something great about using this operation is that you can sort and highlight your text more efficiently, just as expected. In addition, if there is any data within the selected cells, you won’t lose the information you have. Instead, it will appear as if they haven't been merged.
Alright, maybe you need something more specific, and the “Merging” operations above are not enough for you; instead, you need to join values more accurately or in a particular way. For that kind of situation, you can use three incredible techniques to combine two or more data strings into one:
This is a trick maybe you didn’t know until reading this article, but when you use the ampersand symbol (&) between values, you can join them in a string like using a formula using the following steps:
Let’s make an example: You want to merge the information from A1: that says “Jane” and B1, which correspond to the last name “Doe” Then, in the C1 cell, you need to type: =A1&B1 so that the result will be “JaneDoe.” However, if you want each value separately, you need to enter a space between double quotes so that the formula will be =A1&” “&B1. So the result will be “Jane Doe.”
Using the ampersand symbol as a concatenation operator can be a convenient and easy method to combine data, especially when having separated information in several rows or columns, because you can combine data from different cells even if they’re not continuous.
This is another formula you can use to combine your data and can be quicker than using the ampersand symbol as a concatenation operator. This method is advantageous, particularly when having a lot of data in three or more columns or cells. You only have to select the cell you want the combined data from and type the following syntaxis:
=CONCAT(text1,Text2,text3…) or =CONTACT(Text1:Text3). The last one helps you when working with ranges.
Example: =CONTACT(A1:B1) “JaneDoe”
You have to be careful with this one because, in this case, it won't separate the string data if you have space in the central data cells. But if you require adding extra characters, you must enter them within double quotes and separate them with commas.
Example: =CONACT(A1,”-”,B1) “Jane-Doe”
If the last two functions were helpful, the TEXTJOIN operation would be revolutionary. This is even more efficient than the CONCAT since it allows you to establish repetitive delimiters according to the following syntaxis quickly:
Let’s explain each code:
Example: =TEXTJOIN(“-”,TRUE,A1:B1) “Jane-Doe.”
There you have it! Now merging and combining data can be more straightforward every time, so try these methods and find which can fit you better!