We are used to working with whole excel cells within columns and rows. At times, you find yourself working with data imported from other sources or even working with data that is not in the format you are used to. Such data can appear in one cell alone (especially when working with texts with limited commas). Most beginners using Microsoft Excel spreadsheets may not be aware that it is possible to split a cell into two or multiple smaller ones to solve such a dilemma. Unfortunately, I do not mean to split one cell into two or numerous cells figuratively. By dividing cells in Excel, we suggest adding a new column, changing the column widths, and merging two cells into one.
Splitting excels cells helps provide better sorting and filtering features for your data. Using the Unmerge Cells, Text to Column feature, and Flash Fill features, you will be able to split Excel cells. In this article, we learn how to separate Excel cells using different methods. Let's get started.
Method 1: splitting cells using the Delimiter with Text to Column feature
1. In an open Excel workbook, click and select all the cells you want to split.
2. from the main menu ribbon, click on the Data tab.
3. Under the Data Tools tab, select the 'Text to Columns' option to display the 'Convert Text to Columns Wizard – Step 1 to 3' dialog box.
4. In the dialog box, for step 1, check on the 'Delimited' option checkbox. Click Next to proceed.
5. Under the' Delimiters' section, step 2 of the wizard dialog box, specify the delimiter you want to split. You will be able to see a preview of any applied delimiters you select in the 'Data preview' field. Click Next to proceed.
6. In the 3rd step, select a Destination for your selected cells data.
7. Click Finish. Every text or value string in that cell will split into different cells.
Method 2: splitting cells using VBA macro
1. In this example, I will split the cells with the following sentences using VBA macro
1. Go to the Developers tab and click on the 'Visual Basic' option or (Press Alt+F11 to access the visual basic editor)
2. The 'Visual Basic Editor' window will be displayed.
3. Click on the Insert tab. From the given options, select Module to create a new module
4. Type in VBA code into the 'Code Window.'
Public Sub SplitName()
X = Cells(Rows.Count, 1).End(xlUp).Row
For A = 1 To X
B = InStr(Cells(A, 1), " ")
C = InStrRev(Cells(A, 1), " ")
Cells(A, 2) = Left(Cells(A, 1), B)
Cells(A, 3) = Mid(Cells(A, 1), B, C – B)
Cells(A, 4) = Right(Cells(A, 1), Len(Cells(A, 1)) – C)
Next A
End Sub
5. Click the Run tab and select Run Macro to display a dialog box. (Or simply press F5 to run the code)
6. In the 'Macro' dialog box, click the Run button.
Method 3: using the flash fill feature
1. In your open workbook, select empty cell columns right beside the main column you want to split.
2. In the first cell of the first column, type in the first value or text you want to split and press Enter.
3. Type the second name in the second cell. Flash fill feature will appear. If not Go to the Data tab on the main ribbon, click on the Flash Fill option indicating the cell you had typed in. you will see that the first part (all values or texts) in the main column has been split automatically in the adjacent column.
4. Carry on the process for the other columns to separate all text or value strings.
Method 4: splitting cells by using the unmerge cells option in Excel.
While working on imported Excel files, you may find the data merged, or some cells are combined. An easy way to split these cells is by clicking on the Unmerge Cells options. To do this;
1. In your open workbook, select all the cells you need to split.
2. In the Home tab, under the Alignment group, click on the Merge & Centre drop-down arrow.
3. Select Unmerge Cells, and this will divide your merged cells.
Conclusion
The article below gives you different methods on how to split or divide cells. As a constant Excel user who comes across cells you need to separate, you can select either of the methods above to achieve this.