Contents
In this article, you will find several ways to split cells or entire columns in Excel 2010 and 2013. The given examples and screenshots illustrate the work with the “Text by Columns” and “Instant Fill” tools, in addition, you will see a selection of formulas for splitting names, text and numeric values. This tutorial will help you choose the best method for splitting data in Excel.
Generally speaking, the need to split cells in Excel can arise in two cases: First, when importing information from some external database or from a web page. With this import, all records are copied into one column, but you want them to be placed in different columns. Secondly, when splitting an existing table, in order to be able to fine-tune the filter, sorting, or for a more detailed analysis.
Splitting cells in Excel using the Text to Columns tool
Tool «Text by columns” is really very handy when you need to split data from one column into multiple columns in Excel 2013, 2010, 2007 or 2003.
“Text in columns” allows you to split cell values separated by separators, or highlight fixed-width data (when all values contain a certain number of characters). Let’s look at these options in more detail:
Split delimited text data by columns in Excel
Suppose there is a list of participants invited to a conference or some other event. The figure below shows that the column Regular fee (Participant) lists participants names, state and expected date of arrival:
It is necessary to break this text into separate columns so that the table has the following data (from left to right): First Name (Name), Last Name (Surname), Country (Side), Arrival Date (Expected date of arrival) and Status (Status).
- If the table has at least one column to the right of the column that you want to split, then first create new empty columns in which the received data will be placed. This step is necessary so that the results are not written over existing data. In our example, immediately after the column Regular fee there is a column Statusand we are going to add new columns between them Last Name, Country и Arrival Date.If someone forgot, I will remind you of a quick way to insert multiple columns at once into an Excel sheet. To do this, select a column Status, by clicking on its heading, and holding down the left mouse button, drag the pointer to the right to highlight the desired number of columns (how many you want to insert). Then right-click on the selected area and select the command from the context menu Insertion (Insert).
The result will be something like what you see in the figure below (new columns are inserted to the left of the selected columns):
Note: If you don’t have columns immediately following what you want to split, then this step is not necessary and can be skipped. The main thing to keep in mind is that there should be no less empty columns than the number of columns into which you want to split the data.
- Select the column you want to split. Then open the tab Data (Data) > Data Tools (Working with data) > Text to Columns (Text by columns).
- A dialog box will open Convert Text to Columns wizard (Wizard for distributing text into columns). In the first step of the wizard, you choose the data format. Since the records are separated by spaces and commas, we choose the format Delimited (With separators). Option Fixed width (Fixed width) will be discussed a bit later. When everything is ready, click Next (Next) to continue.
- In the next step, we define the separators that are contained in the data and the line terminator.
- Adjustable dividers. If the data is separated by one or more separators, then you need to select all the appropriate options in the section Delimiters (The separator character is) or enter your own version of the separator in the field Other (Other). In our example, we choose Space (Space) and Comma (Comma), and also check the box next to the parameter Treat consecutive delimiters as one (Consider consecutive delimiters as one). This option will help avoid unnecessary splitting of data, for example, when there are 2 or more consecutive spaces between words.
- Set up a line terminator. This option may be needed if the column you are splitting contains any values enclosed in quotation marks or apostrophes, and you want such sections of text not to be split, but treated as whole values. For example, if you select a comma as the delimiter and quotation marks (“) as the line terminator, then any words enclosed in quotation marks (such as “California, USA”) will be placed in one cell. If, however, you set the line terminator to none (No), then “California” would be placed in one column and “USA” in the other.
At the bottom of the dialog box is an area Data preview (Sample data parsing). Before you press Next (Next) It would be wise to scroll through this field and make sure that Excel has correctly arranged all the data into columns.
- Adjustable dividers. If the data is separated by one or more separators, then you need to select all the appropriate options in the section Delimiters (The separator character is) or enter your own version of the separator in the field Other (Other). In our example, we choose Space (Space) and Comma (Comma), and also check the box next to the parameter Treat consecutive delimiters as one (Consider consecutive delimiters as one). This option will help avoid unnecessary splitting of data, for example, when there are 2 or more consecutive spaces between words.
- There are only two things left to do – choose a data format and specify where to put the split cells. In the section Column data format (Column Data Format) You can select the data format separately for each column where the split data will be placed. The default format for all columns is General (General). We will leave it unchanged for the first three columns, and for the fourth column we will set the format Data (Date), which is logical, because this column will contain arrival dates. To change the data format for each specific column, select it by clicking on it in the area Data preview (Data parsing sample) and then set the desired format in the section Column data format (Column data format).
At the same step of the wizard, you can choose in which column to place the split data. To do this, click on the range selection icon (in Microsoft terms, this icon is called Collapse Dialog) to the right of the field Destination (Place In) and select the leftmost column where you want to place the split data. Unfortunately, it is not possible to import split data into another worksheet or workbook, attempting to do so will result in an end link selection error message.
Tip: If you do not want to import any column(s) shown in the Data preview (Data parsing sample), then highlight it and select the option Do not import column (Skip column) in section Column data format (Column data format).
- Press Finish (Ready)!
Breaking Fixed Width Text into Multiple Columns
If the data consists of text or numeric values with a fixed number of characters, you can split it into multiple columns in the following way.
For example, there is a list of products with an ID and a name, and the product ID is 9 characters that appear before the name of this product:
Here’s what you need to do to split such a column into two:
- Run the tool Text to Columns (Text by columns), as we did in the previous example. In the first step of the wizard, select the option Fixed width (fixed width) and click Next (Further).
- In section Data preview (Data parsing sample) adjust the column widths. As you can see in the figure below, the edge of a column is symbolized by a vertical line, and to set the edge of the next column, simply click in the right place. Double clicking on the vertical line will remove the column edge, and if you need to move the column border to another location, just drag the vertical line with the mouse. In fact, all these instructions are detailed at the top of the dialog box 🙂Since each product ID contains 9 characters, set the column border line to this value, as shown in the image above.
- In the next step, select the data format and specify the cells where to put the result, as was done in the previous example, and then click Finish (Ready).
Since each product ID contains 9 characters, set the column border line to this value, as shown in the image above.Split merged cells in Excel
If you have merged several cells in an Excel worksheet and now want to break them back into separate columns, click the tab Home (Home) and in the command group Alignment (Alignment) click the little black arrow next to the button Merge & Center (Combine and place in the center). Next, select from the dropdown list Unmerge Cells (Unmerge cells).
Thus, the merging of cells will be canceled, but the pleasure of the result will be spoiled by the fact that all the data will remain in the left column. I think you guessed that you need to use the function again Text to Columns (Text by Columns) to split data from one column into two or more columns.
Splitting Data into Multiple Columns in Excel 2013 Using Flash Fill
If you’ve already upgraded to Excel 2013, you can take advantage of the new “Instant Fill” and force Excel to automatically fill in (in our case, split) the data when a certain pattern is detected.
If you are not familiar with this function, I will try to briefly explain its essence. This tool analyzes the data you enter into a worksheet and tries to figure out where it came from and if there is any pattern in it. As soon as Flash Fill recognizes your actions and calculates a pattern, Excel will suggest an option, and a sequence of records in a new column will appear in just an instant.
Thus, with this tool, you can take some of the data in one or more columns and enter it into a new column. I think you will better understand what I am talking about from the following example.
First of all, make sure the Flash Fill tool is enabled. You will find this option on the tab Fillet (File) > Options (Settings) > Advanced (Optional) > Automatically Flash Fill (Automatically perform instant filling).
Now let’s see how you can automatically break data into cells. So, you have turned on the Instant Fill tool, and you begin to enter data from the keyboard that you want to place in individual cells. As you type, Excel will try to recognize the pattern in the values you enter, and once it recognizes it, the data will automatically be inserted into the rest of the cells. To understand how this works, look at the figure below:
As you can see, I only entered a couple of names in the column B, and Instant Fill automatically filled the rest of the cells with the names from the column A. If you are happy with the result, just click Enter, and the entire column will be filled with names. A very smart tool, isn’t it?
If Flash Fill is enabled but doesn’t offer any options that match a particular pattern, you can run this tool manually from the tab Data (Data) > Flash Fill (Instant Fill) or by pressing a keyboard shortcut Ctrl + E.
How to split cells using formulas in Excel
There are formulas that can be very helpful when it comes to splitting cells or columns of data in Excel. In fact, the following six functions will suffice in most cases − LEFT (LEFTSYM), MID (PSTR), RIGHT (RIGHT), FIND (FIND), SEARCH (SEARCH) and LEN (DLSTR). Later in this section, I will briefly explain the purpose of each of these functions and provide examples that you can use in your Excel workbooks.
Example 1
The most common situation where these formulas may be needed is the need to split names from one column into several. The figure below shows what result we are trying to achieve:
You can easily split such names into two columns using the following formulas:
- Retrieve the name (First name column):
=LEFT(A2,SEARCH(" ",A2,1)-1)=ЛЕВСИМВ(A2;ПОИСК(" ";A2;1)-1) - Retrieve the last name (Last name column):
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))=ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(" ";A2;1))
For those who are wondering what these formulas mean, I will try to explain in more detail.
SEARCH (SEARCH) or FIND (FIND) are absolutely identical functions that search for the position of a specific text string in a given cell. Formula syntax:
=SEARCH(find_text,within_text,[start_num])
=ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
As arguments, you must specify: what to find, where to look, as well as the position of the character from which to start the search. In our example SEARCH(» «,A2,1) or SEARCH(” “;A2;1) says that we want to find the space character in the cell A2 and start searching from the first character.
Note: If the search starts from the first character, you can omit the argument altogether start_num (start_position) in the formula and simplify it to this form:
=LEFT(A2,SEARCH(" ",A2)-1)
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1)
LEFT (LEFT) and RIGHT (RIGHT) – returns the left or right part of the text from the given cell, respectively. Formula syntax:
=LEFT(text,[num_chars])
=ЛЕВСИМВ(текст;[количество_знаков])
We specify as arguments: what text to take and how many characters to extract. In the following example, the formula will extract the left side of the text from the cell A2 up to the position of the first space found.
=LEFT(A2,SEARCH(" ",A2)-1)
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1)
LEN (DLSTR) – counts the length of the string, that is, the number of characters in a given cell. Formula syntax:
=LEN(text)
=ДЛСТР(текст)
The following formula counts the number of characters in a cell A2:
=LEN(A2)
=ДЛСТР(A2)
If the names in your table contain patronymics or suffixes, then slightly more complex formulas will be required using the function MID (PSTR).
Example 2
Here are the formulas to use when the names you want to break contain a middle name or only one middle initial in the middle.
| A | B | C | D | |
| 1 | Full name | First name | Middle name | Last name |
| 2 | Sally K. Brooke | Sally | K. | Brooke |
- Extracting the name:
=LEFT(A2,FIND(" ",A2,1)-1)=ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-1) - We extract the middle name:
=MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1)) - Extracting the last name:
=RIGHT(A2,LEN(A2)- FIND(" ",A2,FIND(" ",A2,1)+1))=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))
Function MID (PSTR) – extracts part of a text string (that is, a given number of characters). Syntax:
=MID(text,start_num,num_chars)
=ПСТР(текст;начальная_позиция;количество_знаков)
As arguments to the function, we specify: what text to take, the position of the character from which to start, and how many characters to extract.
Example 3
You can use similar formulas to split names with suffixes at the end:
| A | B | C | D | |
| 1 | Full name | First name | Last name | Suffix |
| 2 | Robert Furlan Jr. | Robert | Furlan | Jr. |
- Extracting the name:
=LEFT(A2,FIND(" ",A2,1)-1)=ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-1) - Extracting the last name:
=MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1)) - Retrieving the suffix:
=RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1))
Example 4
And here are the formulas that allow you to break up names with a surname in front and a comma separated from the name, and a patronymic at the end:
| A | B | C | D | |
| 1 | Full name | First name | Middle name | Last name |
| 2 | White, David Mark | David | Mark | White |
- Extracting the name:
=MID(A2,SEARCH(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))=ПСТР(A2;ПОИСК(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-(НАЙТИ(" ";A2;1)+1)) - We extract the middle name:
=RIGHT(A2,LEN(A2)- FIND(" ",A2,FIND(" ",A2,1)+1))=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)) - Extracting the last name:
=LEFT(A2,FIND(" ",A2,1)-2)=ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1)-2)
Example 5
As you understand, these formulas work not only for splitting names in Excel. You can use them to split any data from one column into multiple ones. For example, you can use the following formulas to split text data separated by commas:
| A | B | C | D | |
| 1 | Full designation | Staff | Abbreviation | Столица |
| 2 | Alabama, AL, Montgomery | Alabama | AL | Montgomery |
- Retrieve the state name:
=LEFT(A2,SEARCH(",",A2)-1)=ЛЕВСИМВ(A2;ПОИСК(",";A2)-1) - Extracting the state abbreviation:
=MID(A2,SEARCH(",",A2)+2,SEARCH(",",A2,SEARCH(",",A2)+2)-SEARCH(",",A2)-2)=ПСТР(A2;ПОИСК(",";A2)+2;ПОИСК(",";A2;ПОИСК(",";A2)+2)-ПОИСК(",";A2)-2) - Retrieve the state capital:
=RIGHT(A2,LEN(A2)-(SEARCH(",",A2,SEARCH(",",A2)+1)+1))=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(",";A2;ПОИСК(",";A2)+1)+1))
And here is an example of real data from Excel 2010. The data from the first column is broken into three separate columns:
