Text To Columns - How To Split Or Divide Cell Contents In Excel

Table of Contents[Show]
Text To Columns - How To Split Or Divide Cell Contents In Excel


How do you separate or divide the contents of cells arranged in a column in Excel into several columns?

In general, when you use excel, there are times when you want to separate the contents of an excel cell into different columns based on certain characters or punctuation, for example space (), comma (,), semicolon (;), dash (-) and so on. This kind of case occurs, for example, when you get a file that contains data on the place and date of birth together in a certain column. Then you want to separate the place and date of birth in a different column so that it can be used as a reference for calculating age or for other purposes.

Another case, for example, you get a file imported from another source in CSV format (Comma Separated Values) where the information or data in the CSV format file is usually in 1 column then you want to divide or separate the information in the file into several different columns so that the data that you process will be easier to (Sort), filter based on certain criteria or perform further analysis of the data.

For this kind of need, Microsoft Office Excel has provided a special feature called Text to Columns.

Then how do you use Text to Columns in Excel? Let's study together.

1) How to Split Cell with Text to Columns 

How to Split Cells in Excel with Text to Columns Feature

Text to Columns in Excel is a feature that is used to divide or separate the contents of a cell into 2 or more columns based on certain dividers or delimiters such as commas (,), semicolons (;), dashes (-) and spaces ( ) or separate them based on a certain character length ( fixed length ).

This Text to Columns feature will allow you to divide a cell based on 2 main criteria:

  1. Separate cells based on a specific (delimiter).
  2. Splits cells based on the length / width of the fixed text (fixed width).

Here's a further explanation of how to divide cells based on these two criteria.

Separating Cell Contents Based on Delimiter

For example, suppose you have a data order for goods such as the following:

Separating Cell Contents Based on Delimiter

From this data, it can be seen that the first column is the customer data consisting of the order's name, address and transaction date, separated by characters or punctuation commas (,). While the second column contains the number of orders for goods. 

From this order data we want to separate or divide the contents of each cell in the order column so that it is separated into several columns based on the comma delimiter. 

How to use the Text to columns feature to divide these cells or columns is as follows:  

1) Make sure where you will put the split data results. If after the cell or column that you are going to divide there is data in another column that cannot be overlaid, then insert a number of columns to accommodate the results of the cell separation. For example, I will place the results of cell separation starting from cell B2 so that I insert 4 additional columns and name the columns as needed as follows:

How to use Excel's Text to Columns

* You can skip this step if in the column next to the data that you are separating, no other data may be overwritten.

2) Select the cells that you want to separate, in this example the range A2: A10, then select the menu Text to columns in.

Tab Data--Group Data Tools

Excel's Text to Columns feature

3) Then a dialog box will appear Convert Text to Columns wizard as follows:

How to Use Excel Text to Columns
 

The process of separating or dividing cells with Text to Columns has 3 steps. In this first stage you need to determine how the cells will be divided or separated in several columns. Because we want to separate cells based on delimiters or a certain separator (comma), then select the Delimited option as in the example image above. Next click. Next

4) In the second step, you will be asked to determine what delimiter or boundary mark you use as the criteria for separating the cell or column. The delimiter options provided are the ones that are commonly used, namely Tab, Semicolon (;), Comma (,), and Space (). If you use another delimiter such as a slash (/) or a vertical line (|), enter these characters in the box next to the option Other:

In the example at this stage select commas and spaces as delimiters and make sure the sections are Treat consecutive delimiters as one checked and the sections Text qualifier show double quotes (") as in the example, or please set them as needed if you use different data.

Split Cell by Delimiter

  • Treat consecutive delimiters as one: If your data may contain two or more consecutive delimiters, it's a good idea to make sure this option is checked.
  • Text qualifier: Use this option when multiple text is enclosed in single or double quotes, and you want such sections of text not to be separated.
  • Data preview: Look at this section before you click NEXT to proceed to the next step to make sure that the Text to Columns feature separates the data properly the way you want.

To continue click NEXT.

5) In the third step, you will be asked to determine the format for each column of the cell splitting results and determine where the cell division results will be placed.

How to Split Cell in Excel

  • Column data format: By default, Text to Columns applies the general format for each column that is the result of data separation. In most cases the general format can fulfill the need. However, in certain cases sometimes you need to specify a format for a specific column.

If you want certain columns as a result of data separation to not be displayed, set these columns as do not import column (skip)

  • Destination: To tell Excel or Text to Columns where you want to display or place the cell split result, write manually or place the cursor then select which cell you want to use. Leave this section alone if you want to overwrite your initial data with the new separated data. Or specify other cells that you want.

6) To end this text to Columns wizard, click Finish and watch the results.

How to split cell contents into multiple rows in excel

TIP: If the text to column result is not what you want, use the Undo Menu or the Shortcut to return the results of the cell separation to the original. Ctrl + Z

Separating Cell Contents Based on Fixed Width

If in the previous section we separated cell contents based on a certain delimiter or delimiter character. In this section we will use the Text to Columns feature to separate cell contents based on the number of characters. 

Even though it may rarely be needed, it doesn't hurt you to learn how to use Text to Columns with this fixed with option. 

For example, suppose you have ID number data as below, then you want to divide it into 3 columns. The first column takes 2 characters, the second column takes the next 4 characters, and the rest for the third column.

Text to column fixed width excel

The steps for using Text to Columns to separate cell contents based on the number of characters are as follows:

1) Select the data range or column that we will separate, then activate the Text to Column feature as before.

2) After it appears Convert Text to Columns wizard, select fixed width to separate the cell contents based on the number of characters. Then click NEXT.

Fixed Width Function in Excel Text to Columns


3) In the data preview section, set the position where the cell contents will be separated / divided. Click on the position you want and add additional dividing lines at other positions if needed. To change its position, click and hold the line then slide it to the right or left as needed. To delete it use double click on the line.

Consider the following example to set the separation of cell contents at the 3rd and 7th characters.

Fixed Width Function in Excel Text to Columns

4) When it's finished, you can click Finish to end the splitting of data or click NEXT if needed to set the formatting and placement position of the split data as previously described.

Fixed Width Function in Excel Text to Columns

 

If you get the message “There’s already data here. Do you want to replace it “When using the Text to Columns feature, this message indicates that the cell splitting will cause some other data to be overwritten, if you are sure this process is correct please select OK or Cancel to reset the cell separation process. 

There's already data here.  Do you want to replace it?

If you are in doubt Ok, just choose, anyway if something is wrong you can press to restore data to the original. How easy it is? Ctrl + Z

Apart from using the Text to Column feature, if you are using excel 2013 or excel 2016 you should also try to use the Flash Fill feature which can also be used to separate the contents of this kind of cell.

With excel formulas, how do you separate the contents of a cell into several parts?

Leave a Comment