When working with data stored in Google Sheets, you may need to split or combine multiple cells. This allows you to have increased functionality required for several different procedures. This technique of splitting or combining cells can be helpful with empty cells, cells with textual data, and cells with numerical data.
Moreover, multiple methods are available for splitting or combining cells in Google Sheets. Selecting one for yourself depends on the appropriate scenario. Here is an article for you with some other helpful Google Sheets tricks. In this article, we will go over multiple methods of splitting and combining cells. After going through all of them, you can select one for each use case.
Splitting cells in Google Sheets
In Google Sheets, there can be multiple reasons why you need to split cells. There can be cases when this action is imperative for the desired outcome. More often than not, the data stored in Google Sheets is imported from some external source. This can be from the internet, other devices, or a program stored locally on your system. Once this external data gets imported into Google Sheets, it gets represented in unique formats that the user does not prefer.
Moreover, when you need to further analyze or work on the data, you will have to store it precisely like it was in the actual source. Otherwise, your proceedings or functions can get affected severely. To get rid of this problem, you might have to split the cells in your sheet.
One of the most common cases for splitting cells is when you need to divide the first and last names of individuals. This can also be the case when a long address must be divided into multiple cells. It does not only make your data more usable but also lets you edit the particular components independently. With this, you add more customization points to your sheet, allowing you to manage it more efficiently. If you know how and when to split cells, you will find the respective utility quite frequently. For splitting the contents of a cell, you will have to follow the steps mentioned below,
Step 1: Open the desired sheet on “Google Sheets”.
Sheet for splitting cell
Step 2: Select that particular cell inside the sheet from where you want to display separated results.
Selecting a cell for split results
Step 3: Type “=SPLIT(A1,” ”)” in the “Formula Bar”. Press the Enter key. Make sure to replace A1 with the cell you want to split.
Implementing SPLIT function
Step 4: After you press the Enter key, the respective cells will be filled with the data in A1.
Name split in three cells
Note: This formula displays dynamic results in the new cells. The data in these cells changes whenever the original cell is modified. However, this can be altered by freezing a row manually.
You can use the same function to split a cell with other conditions. The cell will split every time a space appears in the format we used. This is the most common way to split a cell in Google Sheets. You can change this condition to adjust your specific requirements.
Combining cells in Google Sheets
Just like splitting cells, combining them is also somewhat helpful. Most of the use cases of combining cells are to do with reversing what you achieve by splitting them. If you import a list of names with the first and last names appearing in different columns, you might need to merge them into a single file. The cells are also combined whenever you want to center a sheet title. Google Sheets lets you center the text within one cell, but it is of no use if you will center it in the middle of multiple columns. In this situation, there is no other option but to merge various cells for the title and then center it within the new cell. Please note that you cannot just widen the column, affecting all rows.
Here, it would help if you kept in mind that there is a risk of losing data whenever you combine multiple cells. For this very reason, you need to be careful with the method you employ for combining cells in Google Sheets. Overlooking seemingly unimportant steps can lead you to unpleasant circumstances. Also, one must not ignore that you can lose function results when combining or splitting cells in Google Sheets. Therefore, these steps are only to be followed when you ensure no loss to any progress.
1. Combining cells without separator
To combine the contents of two cells without any separator between them, you can use the Google Sheets functions and formulas. These implementations allow you to concatenate the data stored in two cells and then display the combined form in the third cell. To do so, you will have to follow the subsequent steps,
Step 1: Open the desired sheet on “Google Sheets”.
Sheet for combining without separator
Step 2: Select that particular cell inside the sheet to display the combined result.
Selecting a cell for combining without a separator
Step 3: Type “=A1&B1” in the “Formula Bar”. Press the Enter key. Make sure to replace A1 and B1 with the cells you want to combine.
Implementing ampersand without separator
Step 4: After you press the Enter key, the respective cell will be filled with the data in A1 and B1 without any separator.
Cells combined without a separator.
Note: This formula displays dynamic results in the new cell. The content in the third cell changes whenever the original cells are modified. However, this can be altered by freezing a cell manually.
In this case, you can see that we can easily combine two cells in Google Sheets with a simple ampersand operator. We can also use the CONCATENATE function to achieve similar results; however, implementing a function is more complicated than employing one operator.
2. Combining cells with separator
To combine the contents of two cells with a separator between them, you can use the same Google Sheets functions and formulas. For separated results, you will have to add in the separator manually. This needs to be done inside the formula bar. For this method, you will have to observe the following steps,
Step 1: Open the desired sheet on “Google Sheets”.
Sheet for combining with separator
Step 2: Select that particular cell inside the sheet to display the combined result.
Selecting a cell for combining with separator
Step 3: Type “=A1&”-”&B1&”-”&C1” in the “Formula Bar”. Press the Enter key. Make sure to replace A1, B1, and C1 with the cells you want to combine.
Implementing ampersand with separator
Step 4: After you press the Enter key, the respective cell will be filled with the data in A1, B1, and C1 with the separator you added.
Cells combined with a separator
Note: This formula displays dynamic results in the new cell. The content in the fourth cell changes whenever the original cells are modified. However, this can be changed by freezing a cell manually.
This implementation combines three cells and a separator between them. With this method, you can combine as many cells or separators as you want. This means that this process is scalable, which increases its usability. We can also use the CONCATENATE function to achieve similar results; however, implementing a function is more complicated than employing one operator.
3. Combining cells with line breaks
In some cases, you need to combine the contents of different cells within a single cell but in separate lines. As we discussed before, this is particularly useful when dealing with addresses. You can think of the line break as a separator for this method. To combine multiple cells with line breaks in Google Sheets, you will have to undertake the following steps,
Step 1: Open the desired sheet on “Google Sheets”.
Sheet for combining with line breaks
Step 2: Select that particular cell inside the sheet to display the combined result.
Selecting a cell for combining with line breaks
Step 3: Type “=A1&char(10)&B1&char(10)&C1” in the “Formula Bar”. Press the Enter key. Make sure to replace A1, B1, and C1 with the cells you want to combine.
Implementing ampersand with line breaks
Step 4: After you press the Enter key, the respective cell will be filled with the data in A1, B1, and C1 with line breaks in between.
Cells combined with line breaks
Note: This formula displays dynamic results in the new cell. The content in the fourth cell changes whenever the original cells are modified. However, this can be changed by freezing a cell manually.
As you can easily observe, “char(10)” adds a line break in the cell. This trick can be used without the ampersand operator as well. We can also use the CONCATENATE and TEXTJOIN functions to achieve similar results; however, implementing a function is more complicated than employing one operator.
Conclusion
The methods of splitting and combining cells in Google Sheets are not that complicated. You can split or combine cells in this program effortlessly. This allows you to manage the data in your sheets however you want. It adds to the functionality of Google Sheets and lets you present the text and numbers in unique ways. After learning how to split and combine cells, you can format your sheets like you never did before.
