How to Split a Single Cell in Excel (Easiest Way in 2024)
In this article, we will show you how to split a single cell in Excel using the “text to columns” tool and the TEXTSPLIT function. Simply follow the steps below.
Split Single Cell in Excel Using “Text to Columns” Tool
Follow the steps below to split a single cell in Excel using “Text to Columns” Tool.
1. Select the Cell to Split
Begin by clicking on the cell containing the data you wish to split into multiple cells. In the example below, we will split full names into last names and first names.
2. Navigate to the “Data” Tab
Locate and click on the “Data” tab at the top of the Excel window to access data-related tools.
3. Access the “Text to Columns” Tool
Under the “Data Tools” section, find and select “Text to Columns”. This will enable you to split the selected cell.
4. Choose How to Split the Single Cell
In the “Convert Text to Columns Wizard” window, decide how Excel should split the data. Opt for “Delimited” if the data is separated by a specific character, or “Fixed width” if the data has consistent width.
If you’ve chosen “Delimited,” indicate the character(s) that separate the data. Check the relevant box(es) for the delimiter(s) used, or specify a custom delimiter if necessary.
5. Specify the Column Data Format (If Needed)
You can additionally indicate the “Column Data Format” to customize how your data is previewed. Then, choose where you want to place the split cells. If satisfied, click “Finish” to apply your chosen settings.
6. Review and Confirm Split
Review the preview provided in the lower section of the window to ensure Excel will split the cell correctly.
Split Single Cell in Excel Using “TEXTSPLIT” Function
Follow the steps below to split a single cell in Excel using “TEXTSPLIT” Function.
1. Select the Cell Containing Text to Split
To begin, click on the cell in Excel that holds the text you want to divide. This could be any cell within your worksheet.
2. Input the TEXTSPLIT Formula
In the formula bar, type =TEXTSPLIT( followed by the cell reference containing the text to split. This could be any character or symbol that separates the parts of the text. For instance, if the text is in cell A2, you’d enter “=TEXTSPLIT(A2,”. Then, add a comma “,” and specify the delimiter.
Enter the Formula: =TEXTSPLIT(A2,”,”)
3. Verify the Split Results
After pressing Enter, Excel will split the text in the selected cell according to the specified delimiter. Each part of the split text will occupy its own cell in the same row. Review the cells to ensure the text has been separated correctly.
4. Copy the Formula to the Remaining Cells
You can replicate the formula for the remaining rows to split a single cell in Excel. If the text wasn’t divided as expected, you may need to adjust the delimiter in the TEXTSPLIT formula.
We hope you now better understand how to split a single cell in Excel. If you enjoyed this article, you might also like our article on how to remove split in Excel or our article on how to split a cell diagonally in excel and fill half with color.
