How to Split Cells in Excel Using Delimiter and Fixed Width
By Indeed Editorial Team
Published 1 April 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Excel is a powerful tool for data visualisation and analysis that has several functions to help users rearrange data. One commonly used Excel function is splitting cells. If you're interested in roles that utilise Excel as part of their work duties, such as financial analysts and accountants, it can be beneficial to know how to split cells in Excel. In this article, we explain what it means to split cells in Excel, explore why you may split cells and provide a step-by-step guide on how to split cells using the delimiter and fixed width functions.
What does it mean to split cells in Excel?
When you split cells in Excel, you're turning one cell into multiple cells. This Excel feature allows users to rearrange the format of their spreadsheet. This includes information that is present in the cell that's being split. Using Excel's delimiter option, you can use spaces or punctuation, such as commas, full stops or dashes to split information from within one cell into separate cells. You can also use Excel's fixed width feature to split information based on character length.
Why may you split cells in Excel?
Raw data often comes in different formats that may not be compatible with Excel's standard cell structure. When transferring raw data into Excel, such as copying and pasting text from a different file type, it often results in unorganised formatting. For example, you may have a table with a lot of columns of different categories and after transferring it into Excel, all the information is only inside one column. This can make it difficult to perform data analysis and use other basic functions of Excel, such as sorting and filtering.
There are also situations where you perform data entry using a specific format and decide after completing all the entries to further split certain categories. For example, you may have initially chosen to include full names in a single column and, only after entering all the names, realise that the analysis you're performing requires first and last names to be in separate columns.
Related: What Does a Data Analyst Do?
How to split cells using the delimiter function
You can follow these steps to split cells using Excel's delimiter function:
1. Select the cells you want to split
Open the Excel spreadsheet you want to edit and click on the cells you want to split. You can click to select a single cell. If selecting multiple cells, you can either drag the mouse cursor across the cells you want to select or hold shift and click on the first and then the last cell in the range of cells you want to split. A thick border appears around the cells you've selected and the cells within become greyed out.
To split information within cells using the delimiter method, ensure you've separated all the data with spaces or punctuation. For example, if you have a cell that contains Hong Kong City and you want Hong Kong and City separated into different cells, you can add a comma in between so it looks like "Hong Kong, City".
2. Select "Text to Columns" function
Once you've selected the cells you want to split and ensured you've formatted the text correctly, click the "Data" tab. This is in the menu bar at the top of an Excel spreadsheet, in between the "Formulas" and "Review" tabs. After selecting the "Data" tab, there's a "Text to Columns" button you can click.
3. Select the "Delimited" option
Clicking the "Text to Columns" button opens the "Convert Text to Columns Wizard" window. The first step of this function is checking the "Delimited" or "Fixed width" option. Excel usually selects the "Delimited" option by default, otherwise, remember to click the checkbox. After selecting "Delimited", you can click the "Next" button which moves you to a new window where you can select the delimiters.
4. Choose your delimiters
The delimiter is the symbol that Excel uses to know where to split cells. For example, if you select commas as a delimiter, Excel separates the cell with "Hong Kong, City" into two cells with "Hong Kong" and "City". The available default delimiters are tab, semicolon, comma and space, but you can also select "Other" and enter the symbol you want to use as a delimiter into the text box.
You can select more than one delimiter depending on how you've formatted the data. Once you've selected your delimiters, Excel provides a "Data preview" tab at the bottom of the window that shows a sample of how the cell data is going to be split. Once you're satisfied with the preview, you can click "Next" again to move to the final step.
5. Pick formatting options
The last step is choosing how you want the new columns to be formatted. You can specify the data format of the new columns in the "Column data format" section. Using the "General" option usually works best as it retains the original format of the data. Click the "Destination" button if you want to select specific cells to place the new columns in your spreadsheet.
For instance, if your current information is in cell "C2" and you want the new cells next to it, type in "D2". If you don't select a specific destination, by default Excel overwrites the selected cells with the split cells. After determining where to place your columns, press the "Finish" button to split your cells.
How to split cells using Excel's fixed width function
You can follow these steps to split cells using Excel's fixed width function:
1. Select cells
Same as with the delimiter function, start by selecting the cells you want to split. Unlike the delimiter method, the information in these cells isn't required to be separated by specific spaces or punctuation. To use the fixed width function properly, it's important to separate information you want to be split into different cells with noticeable gaps. When splitting multiple columns at once, ensure there's a vertical blank space throughout all the rows of the cells you want to split. You can do this by adding spaces where needed.
2. Select "Text to Columns" function
When you have the right cells selected and have properly formatted the data, click on the "Data" tab in the Excel menu. When the "Data" tab appears, select the "Text to Columns" button. This opens the same "Convert Text to Columns Wizard" window as when using the delimiter function.
3. Select the "Fixed Width" option
After the "Convert Text to Columns Wizard" menu appears, select the "Fixed Width" option. The "Preview of selected data" section can help you ensure you've separated the text within each cell properly. Once you're satisfied, press the "Next button" to move to the next step of manually dividing cells.
4. Manually select cell split locations
In the second step of the "Convert Text to Columns Wizard" menu, there's a "Data preview" section that you can interact with. This is where you can manually add where you want to split cells. Here are three ways you can interact with the "Data preview" section:
Create a break line by clicking on the desired position.
Delete a break line by double-clicking on any existing lines.
Move a break line by clicking and dragging an existing line.
Break lines represent where Excel splits the cells you've chosen. For example, if you have a cell with "Hong Kong City" and you add a break line that goes through the space between "Hong Kong" and "City", Excel splits your cell into those two cells accordingly. If you select multiple rows, you can't set specific break lines for each row, which is why it's important to have a distinct vertical gap in all the rows you want to split. You can split a cell into as many columns as break lines that you add. Once you've added the break lines you want, you can click "Next".
5. Pick formatting options
This step is the same as the "Delimiter" function. You can let Excel know where to place the split columns on your spreadsheet by going to the "Destination" option and entering the column and row to place them or let Excel automatically replace the cells you selected. Click the "Finish" button when you're satisfied. You can return to any previous step using the "Back" button if you notice any mistakes in the "Data preview" section.
Tips for splitting cells in Excel
Here are some tips to consider when you're splitting cells in Excel:
Leave cells blank: Leave the cells to the right of the selected cells or selected destination cells empty, because Excel automatically overwrites any content in cells to the right of the cells you're splitting or the destination cells. If you're splitting a cell into four columns, ensure you leave three columns to the right of the selected cells or destination cells blank.
Flash Fill: Depending on your version of Excel, you may have the "Flash Fill" function available, which helps fill in information based on surrounding context. If you've already split a series of cells but have some new data to add, you can use the "Flash Fill" function by pressing "Ctrl+E" to copy the formatting of the splitting you performed.
Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.
Explore more articles
- 12 Leadership Examples For the Workplace
- What Is Staff Turnover? (Plus How To Improve Turnover Rates)
- How to Write a Job Transfer Request (With Examples)
- What is Consulting? Types, Salary and Methods of Consulting
- Co-Branding Strategies for Career Development: Plus Benefits
- What Are Social Skills? (Definition, Examples and Tips)
- Learn How To Ask a Professor for a Letter of Recommendation
- How To Ask for a Day Off (With Email Template and Example)
- Important Defining Traits of a Business Unit (With Benefits)
- What Is Semi-Skilled Labour? (With Definition and Benefits)
- Differences Between Lead Generation vs. Demand Generation
- 10 Management Styles To Lead Effectively: Overview and Examples