In this post, we'll show you how to split cells in excel.

Dealing with data obtained from external sources in Excel? In this case, you may have to work with data that isn't in the format you like. This is especially true for a comma-separated text that is entered into single cells.

At such a time, you will need to divide cells in Excel. These might be instances where you obtain data from a database, copy it from the web, or obtain it from a coworker.

Depending on the data type, there are several ways to divide cells. This article will walk you through the process of cell division step by step for the best outcomes.

Table Of Contents:

• What Is Cell Splitting?
• How To Split Cells In Microsoft Excel?
• Frequently Asked Questions
• Conclusion

What Is Cell Splitting?

Within a new column, you may want to split a cell into two smaller cells. Regrettably, this is not possible in Excel.

Alternatively, add a new column adjacent to the one containing the cell you wish to divide, and then divide the cell. You may also divide a cell's contents into numerous neighboring cells.

Microsoft Excel provides a plethora of tools that may assist you with data management.

Assume you have a dozen cells, each of which is filled with a first and last name. You can use features to divide these 2 data points (first and last name) into two different columns. This way you can organize your data appropriately.

How To Split Cells In Microsoft Excel?

There are two methods for splitting an Excel cell: employing delimiters or a set width. There are some minor variations between these two alternatives.

The Delimited Option

The Delimited option will divide your data using spaces, commas, dashes, periods, or any other formatting marks. All these are ones you may have in a single cell.

For instance, if you write someone's name as "Leena John," it will divide Leena and John into different columns.

Steps to Follow:

• Select the cell, set of cells, or complete column in Excel that contains the text you wish to divide.
• It must include two bits of data separated by a delimiter. This is often a comma, period, dash, or space.
• Select the Data tab, then select Text to Columns.
• Select "Delimited" in the "Convert Text to Columns Wizard" box.
• See the next section if you wish to base the columns on a set width.
• Press the "Next" button.
• Choose one or more delimiters - that is, what Excel should use to determine how to divide the columns. It's usually a comma in cases like names or localities.
• Data imported from another spreadsheet or database may be delimited by a tab or space.
• You can also select more than one delimiter. "Next" should be selected.
• Choose how to style the column and, if desired, use the "Destination" option to designate where the new column will display in the spreadsheets.
• If you do not check this box, the new split columns will override the existing cells.
• Press the "Finish" button.

The Fixed Width Option

The Fixed Width feature allows you to directly select where Excel should split the data. For instance, if you have a list of names, you may instruct Excel to divide the data.

This is done in such a way that one column has the initial letter of each person's name. Then the second list contains the remainder of the name.

Steps to Follow:

• Select the cell, set of cells, or complete column in Excel that contains the text you wish to divide. It is not required to have delimiters.
• Then, on the "Data" tab, select "Text to Columns."
• Select "Fixed width" in the "Convert Text to Columns Wizard" box, then click "Next."
• To divide the text into numerous columns, use the ruler at the top of the "Data preview."
• After you've added a break, you may rearrange it by dragging it.
• You may also construct more than two columns by inserting numerous breaks.
• If you accidentally insert a break that you don't want, double-click it to remove it.
• When you're finished, press the "Next" button.
• Choose how to style the column and, if desired, use the "Destination" option to designate where the new column will appear in the spreadsheet.
• If you do not check this box, the new split columns will override the existing cells.
• Press the "Finish" button.

You may also like:

• How to unhide or hide columns & rows in excel
• How to find duplicates in excel
• How to calculate age in excel

How To Split Cells Using Text To Column?

When data is loaded into Excel, it might be in a variety of formats, depending on the originating program.

So how do you split cells using a text column?

• Choose the cell or column containing the text you wish to divide.
• Select Data > Text to Columns from the menu.
• Select Delimited > Next in the Convert Text to Columns Wizard.
• Delimiters for your data should be selected. For instance, Comma and Space. In the Data preview box, you may see a glimpse of your data.
• Next should be selected.
• In your worksheet, select the Destination, which is where you want the divided data to display.
• Choose Finish.

How To Split Cells Using Text Functions?

Excel Text functions are used for slicing and dicing text strings.

While the Text to Column functionality produces a static output, the result obtained by utilizing functions is dynamic. It will immediately update when the source data is changed.

Use the following formula to find the first name from this list: =LEFT(A2, SEARCH(" ", A2)-1)

This formula would look for the first space character and return all of the text preceding that space character. The search function is used in this formula to determine the location of the space character.

The left function is then used to extract all the characters to the left of it.

If you're looking to extract the last name, apply the following formula: =LEN(A2)-RIGHT(A2)-SEARCH (" ", A2)

This formula employs the search function to locate the position of the spacebar.

It then subtracts that amount from the overall length of the name (that is given by the LEN function). This indicates the number of characters in the last name.

The right function is then used to retrieve this last name.

Use this formula to obtain a middle name: =IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)),"")

The Mid function begins with the first space element. It retrieves the names by comparing the positions of the first and second space characters.

How To Split Cells Using Flash Fills?

Ever noticed the excel 2013 function called Flash Fill? It might come in handy if you have a pattern and want to rapidly remove a portion of it.

Flash fill works by detecting patterns and repeating them in all of the cells. Here's how you use Flash Fill to retrieve the first name from a list:

• Enter a name like Leena John, with the first name in cell B2 (Leena)
• When you pick a cell, you will see a tiny square at the right end of the cell selection. Double-click it. This will fill all of the cells with the same name.
• When the cells are filled, the Autofill Options icon will appear at the bottom right. Simply click on it.
• Choose Flash Fill from the menu.
• When you choose Flash Fill, you'll see that all of the cells refresh and now show the initial name for each name.

The Working of Flash Fill

Flash Fill searches the data set for similarities and copies them. It is a remarkably sensible function that, in most situations, performs as predicted. However, it also malfunctions in other ways.

For instance, suppose I had a list of names with a mix of names, some with a middle name and others without. If you retrieve the middle name in this situation, Flash Fill will offer the wrong name if there is no first name.

It is still a very close representation of the trend. However, that is not what you might have hoped for. However, it is still a useful tool to maintain in your armory and employ as needed.

Frequently Asked Questions

1. What is the shortcut to split a cell in Excel?

Split Cells: Alt + A, then hit P after leaving the key A.

To merge them back, press Alt + A, then leaving the key A and pressing M.

2. How do I put multiple columns under one column in Excel?

Click in a cell to divide it, or choose several cells to split.

Split Cells may be found under Table Tools, on the Layout tab, under the Merge group.

Enter the number of columns or rows into which you wish to divide the chosen cells.

3. How do you split the delimiter in Excel?

Choose the cell or column containing the text you wish to divide.

Select Data > Text to Columns from the menu.

Select Delimited > Next in the Convert Text to Columns Wizard.

Choose the Delimiters for your data.

Next should be selected.

In your worksheet, select the Destination, which is where you want the divided data to display.

Conclusion

And, that's it for our guide on "how to split cells in excel?".

Excel is fantastic for managing data, but files do not often import correctly.

Whenever you try to keep data distinct, you might wind up with a lot of it in a single cell. This is when cell splitting comes into the picture.

The procedure is simple and comparable to cell merging, with a few important differences. So, if you want to divide cells containing names and other data, this article is for you.

Did you find this post useful, or do you believe we left something out? Please share your thoughts in the comments area below.