How to split cells in Excel: Text to Columns, Flash Fill and formulas

How to separate cells in Excel by delimiter

Suppose, you have a list of participants where a participant name, country and expected arrival date are all in the same column:

What we want is to separate data in one cell into several cells such as First Name, Last Name, Country, Arrival Date and Status. To have it done, perform the following steps:

1. If you want to place the results in the middle of your table, start by inserting a new column(s) to avoid overwriting your existing data. In this example, we have inserted 3 new columns like shown in the screenshot below: If you don't have any data next to the column you want to separate, skip this step.
2. Select the cells you want to divide, navigate to the Data tab > Data Tools group, and click the Text to Columns button.
3. In the first step of the Convert Text to Columns wizard, you choose how to split cells - by delimiter or width.In our case, the cell contents are separated with spaces and commas, so we select Delimited, and click Next.
4. In the next step, you specify the delimiters and, optionally, text qualifier.You can choose one or more predefined delimiters as well as type your own one in the Other box. In this example, we select Space and Comma:

   Tips:

   • Treat consecutive delimiters as one. Be sure to select this option when your data may contain two or more delimiters in a row, e.g. when there are a few consecutive spaces between words or the data is separate by a comma and a space, like "Smith, John".
   • Specifying the text qualifier. Use this option when some text is enclosed in single or double quotes, and you'd like such portions of text to be inseparable. For example, if you choose a comma (,) as the delimiter and a quotation mark (") as the text qualifier, then any words enclosed in double quotes, e.g. "California, USA", will be put into one cell as California, USA. If you select {none} as the text qualifier, then "California will be distributed into one cell (together with an opening quotation mark) and USA" into another (together with a closing mark).
   • Data preview. Before you click the Next button, it stands to reason to scroll through the Data preview section to make sure Excel has split all cells contents right.
5. Just two more things are left for you to do - choose the data format and specify where you want to paste the resulting values:
   • Data format. By default, the General format is set for all columns, which works well in most cases. In our example, we need the Data format for the arrival dates. To change the data format for a particular column, click on that column under Data preview to select it, and then choose one of the formats under Column data format (please see the screenshot below).
   • Destination. To tell Excel where you want to output the separated data, click the Collapse Dialog icon next to the Destination box and select the top-leftmost cell of the destination range, or type a cell reference directly in the box. Please be very careful with this option, and make sure there are enough empty columns right to the destination cell.

   Notes:

   • If you do not want to import some column that appears in the data preview, select that column and check Do not import column (skip) radio button under Column data format.
   • It is not possible to import the split data to another spreadsheet or workbook. If you attempt to do this, you will get the invalid destination error.
6. Finally, click the Finish button and you are done! As shown in the below screenshot, Excel has perfectly placed the contents of one cell into several cells:

How to split text of a fixed width

This section explains how to divide a cell in Excel based on the number of characters you specify. To make things easier to understand, please consider the following example.

Supposing, you have Product IDs and Product names in one column and you want to extract the IDs into a separate column:

Since all of the product IDs contain 9 characters, the Fixed width option fits perfectly for the job:

1. Start the Convert Text to Columns wizard as explained in the above example. In the first step of the wizard, choose Fixed width and click Next.
2. Set the width of each column by using the Data preview section. As shown in the screenshot below, a vertical line represents a column break, and to create a new break line, you simply click at the desired position (9 characters in our case): To remove the break, double-click a line; to move a break in another position, simply drag the line with the mouse.
3. In the next step, choose the data format and destination for the split cells exactly as we did in the previous example, and click the Finish button to complete the separation.