3 Quick Methods to Split Cells in Excel

This blog comprise of methods that will assist you in learning how to separate cells in Excel. Split Excel cells into numerous cells by following the step-by-step instructions. This article will show you how to split cells in Excel.

Microsoft Excel includes a plethora of functions that may assist you with data management. Assume you have a hundred cells, each of which is completed with a first and last name. You may use the "Text to Columns" function to divide these two data points (first and last name) into two independent columns, which is useful for organising your data. There may be times whenever you need to divide cells in Excel.

These might be instances where you obtain data from a database, duplicate it from the internet, or obtain it from a coworker. A simple example of when you might need to divide cells in Excel is while you have a list of full names and wish to separate them into first and last names. Or you obtain an address and want to divide it so you can evaluate the cities or the pin code individually.

Learn to Split Cells Diagonally in Excel

There may be times whenever you need to divide cells in Excel. These might be instances where you obtain data from a database, duplicate it from the internet, or obtain it from a coworker.

A simple example of when you might need to divide cells in Excel is while you have a list of full names and wish to separate them into first and last names. Or you obtain an address and want to divide it so you can evaluate the cities or the pin code individually.

For that, you need to know how to split cells in excel. 

Step 1:  First, you need to open your new project, then select the number of cells that you wish to split.

Step 2: Next, tap on the format cells option from the context menu. 

Step 3: In that particular dialog box of format cells, tap on the border tab and then click on the highlight option.

Step 4: Tap the OK button. Now you will notice the diagonal line getting added in the specified cell.

Step 5: Next, type two words in the specified cell as you need to and then highlight the very first word in the cell and then click on the arrow at the bottom-right corner of the Home tab. 

How to Split Data in Cells in Excel

When collecting and analyzing data in Excel that you've exported from these other sites, you may have to change or update that's not in the style you like. This is especially the case for a comma-separated text that is entered into single cells. The only method to deal with that data in Excel is to divide a cell and by knowing how to split cells in excel. Depending on the data type, there are several ways to do this.

This article will teach you how to divide a cell, how to extend that to a full column, and when to use each alternative.

Step 1: First, you need to select the cells whose data you want to split. Then, tap on the data menu.

Step 2: Next, you need to select the text to columns in the data tools group on the ribbon.

Step 3: You will notice that there is a three-step wizard. In the very first window, you need to make sure Delimited is selected and then tap next. 

Step 4:  Just next to the wizard window, you need to deselect the tab and then make sure that you have selected the Space. Tap on next to continue.

Step 5: Next, you need to select the destination field in the next window itself and then in your spreadsheet.

Step 6: Next, you need to tap on the finish and complete the wizard. 

Note: The technique outlined previously works because the text in the data to be divided in the cell was separated by a gap. This text-to-column tool in Excel may also divide a cell if the text is interrupted by a tab, semi-colon, comma, or any other character you choose.

Using Excel Text Functions

Another method for splitting a cell in Excel is to use distinct text functions. Text operations allow you to extract sections of a cell that you may then paste into another cell.

Excel text functions include:

• Left(): Extracts several characters from the text's left side.
• Right(): Extract a number of characters from the text's right side.
• Mid(): Returns a set of characters from the middle of a string.
• Find(): Locates a substring within another string.
• Len(): Returns the total number of characters available in a text string.

You may not need to employ all of these functions to divide cells. However, you may employ these in a variety of ways to get the same result.

For example, you may extract the first name by using the Left and Find functions. The Find feature comes in handy since it can inform you where the delimiting character is located. It's a space in this circumstance.

Conclusion

The final method for splitting a cell in Excel is to use the Flash Fill tool. This necessitates that the cells you're dividing the original one into are immediately next to it. If this is the case, all you need to do is input the portion of the original cell you wish to break out. Then drag the cell's lower right corner down to fill the cell underneath it. When you do this, you'll see a little cell fill icon with a plus sign next to it appear.

Once you do this, you'll notice that the Flash Fill function recognises why you entered what you entered and repeats the procedure in the following cell. This will be accomplished by identifying and writing in the first name in the original cell to the left.

As you can see, there are several approaches to accomplishing the same goal. The method you use to divide a cell in Excel is determined by where you want the final product to go and what you want to do with it. Any choice works, so pick the one that makes the most sense for your scenario and implement it.

You can reach out to our HubSpot experts to troubleshoot any particular issue you’re facing or have a custom HubSpot development requirement. If you’re having short on resources to take care of the CMS Hub configuration and other day to day HubSpot tasks, check out our HubSpot CMS management service by FThemes.

Talk soon!