Split Cells in Excel

One of the most popular spreadsheets is MS Excel, and various sectors have opted for it because of its efficiency and ease of work. It has gained immense popularity since the day it was launched for the common people. MS Excel has various crucial built-in features, and the split cells feature is one of them.

As a corporate worker, the ‘Split Cells’ feature is an integral part of my work that consistently makes my job easier. When I work on projects with large databases, the split cell feature eases my job by simply splitting a single cell into multiple cells in MS Excel. Splitting cells is one of the most basic features of MS Excel that a corporate employee must learn. If you’re still a beginner, consider going through an easy-to-follow Excel tutorial.

To merge or split cells in Excel, you must follow certain steps, functions, and formulas, which I will mention in this guide. Keep reading to learn more!

Importance of Splitting Cells in Excel

While working on MS Excel, I have frequently come across circumstances when I needed to break down a single cell into multiple columns. I have listed some reasons that will help you understand why it is necessary to split cells in Excel:

• To make your spreadsheet easier to read: When a huge amount of data is divided into smaller parts, it is easier for a viewer to understand and scan the project sheet.

• To help ease the data analysis process: This is one of the most important tasks of the split cells feature, as it helps users or viewers easily focus on and analyze small chunks of data.

• To reformat your spreadsheet: You can do so by separating the information from the source by using the split cells feature.
  

How Can You Split Cells in Excel by Using Text to Columns?

In my years of experience, I have observed that splitting cells with the ‘Text-to-Columns’ method is one of the easiest methods. Here are the steps that you must follow:

• Step 1: Select the cell or cells that you want to split. To select more than one number of cells, you have to press 'Shift' and then drag your mouse down to select the cells as shown in the picture.

Source: MS Excel

• Step 2: After selecting the desired cells, you need to locate the 'Data Tab', where you will find the 'Data Tools' option, under this option, you will find the 'Text to Columns' wizard. This feature changes the selected cells into different columns according to your desire.

• Step 3: After selecting the 'Text to Columns' wizard, a dialog box will pop up. Here, you have to select a specific type of file that suits your data. Then, select 'Delimited' and click 'Next.'

• Step 4: In the next step, you must select the delimiters you require in your spreadsheet. For example, space is a crucial delimiter that separates words. Similarly, you can add semicolons, commas, etc., according to your requirements.

Step 5: After selecting the required delimiters, click 'Next'. This will open a dialog box that allows you to add certain advanced settings to your cells. If you do not want to incorporate those settings, select 'General' and click 'Finish'.

Step 6: You have successfully used the 'Text to Column' feature to split cells in Excel.

Splitting Cells in Excel With ‘Flash Fill’

The next method by which you can split cells in Excel is ‘Flash Fill’. This is one of the most commonly used MS Excel functions. The ‘Flash Fill’ feature automatically recognizes the pattern of the text and repeats the actions later.

Here are the steps that you have to follow to split cells in Excel using ‘Flash Fill’:

• Step 1: In the first step, you must go to a new column and write the first word of the original set. For instance, if your original set includes names like 'John Brown', you would write 'John' in one cell and 'Brown' in the cell next to it.

• Step 2: In the next step, you must locate the ‘Data Tab, under which you will find various ‘Data Tools.’ ‘Flash Fill’ is one of the features available under ‘Data Tools.’

• Step 3: Now, select the cell where the first name has been written and click the ‘Flash Fill’ option. It will automatically fill the next cells.

• Step 4: Now, you have to select the cell where the surname has been written and continue the procedure by clicking on the ‘Flash Fill’ feature. Then, you will witness how this feature automatically fills the next cells.

Splitting Cells by using Formulas in MS Excel

In MS Excel, you can easily split a cell using various formulas, also known as text functions. These formulas allow a user to extract certain information from a particular cell and transfer it to a completely new cell.

The text formulas are:

• Left()- This text function extracts characters from the left side of the actual text.

• Mid()- This is the next text function, it extracts many characters from the middle of the actual text.

• Right()- The third text function extracts many characters from the right side of the desired text.

• Find()- This is the second last text function, which is used to find various sub-strings that are included with the main string.

• len()- This is the last text function that extracts the total number of strings present in a character.

For example, if you want to search for some characters that lie on the left side of the string, the formula would be:

LEFT(A2,SEARCH(',',A2)-1)

Formulas help to complete work easily and within the given deadlines. Learn more advanced Excel formulas to improve your work pace.

How Can You Merge and Unmerge Cells in Excel?

Apart from splitting cells in Excel, you also merge and unmerge cells. To merge cells in Excel, you have to follow these steps:

• Step 1: You have to select two cells or more than two cells adjacent to each other that would eventually get merged.

• Step 2: In the next step, click on the home tab, where you will find the Excel merge and center option. Then click on the arrow situated right next to the merge and center option. Then click on the 'Merge Cells' option.

• Step 3: The strings ultimately get merged.
  

The steps to unmerge cells in MS Excel:

• Step 1: In the first step, click on the ‘Home’ tab, where you will find the ‘Merge & Center’ option. After locating this option, click on the arrow situated right next to it. Then click on the 'Unmerge Cells' option.

• Step 2: After clicking the 'Unmerge Cells' option, the selected cells immediately get unmerged or split.
  

In Conclusion

This guide on how to merge and split cells in Excel will help you complete your work or any assigned project with much ease. As you have gathered enough knowledge about split cells, you can share these features with your friends to reduce their pressure and save time.

Excel skills are mandatory, especially if you are pursuing a career in the corporate sector. upGrad offers not only expertly designed Excel tutorials and courses but also other relevant data science courses that can steer your career toward success.

Frequently Asked Questions

1. How do I split a cell into two cells in Excel?

The text-to-column feature helps you split a cell into two. After clicking on this feature, you must select the delimiters for a desirable result.

2. Can you split an Excel cell in half?

In MS Excel, you cannot split a cell in half. However, you can easily split a cell into two different cells.

3. How do I split a single cell into rows in Excel?

To split a single cell into rows, you need to use the text-to-column feature. There, you must use the advanced settings to set the delimiters for you.

4. What is the shortcut for split cells in Excel?

The shortcut for split cells in Excel is ATL+A+E.

5. Can you split an Excel cell into two columns?

Yes, a cell in MS Excel can be split into two columns using the text-to-columns feature. This feature is situated under the Data Tab.

6. How do you split a cell in half in sheets?

MS Excel does not have a feature that allows a user to split a cell in half, while you can split a single cell into two different cells.

7. Can you split a single cell in sheets?

A single cell in MS Excel can be split with the assistance of the text-to-column feature that can be found under the Tool Tab.

8. How do you split a cell in half vertically?

A cell can be split vertically by clicking on the text-to-column feature.