In the article "Excel - Table Optimization" it was stipulated that for correct work with the table, each column should contain only logically indivisible data .
For example, in the table of employees in Fig. 1, it is required to sort the data by last names, and then by the names of employees (to order namesakes). To do this, distribute the surnames, names and patronymics by individual columns.
When you want to separate data that is in the same column, you can use the built-in Excel feature - splitting the contents of one column into several .
To do this, you must perform the following steps:
1. To the right of the range to be divided, insert several empty columns according to the number of data to be separated. This is done to avoid replacing data in adjacent columns. In our case, we will insert two new columns (Fig. 2).
2. Select the column that contains shared data (Last name First name Middle name).
3. On the Data Working with Data panel of the Data tab, click the Text by Columns button.
In the opened dialog box Text Wizard (parsing) - step 1 of 3 (Fig. 3) in area Source Data Format specify split option:
— with separators
- if the text in the cells is separated by spaces, commas, semicolons, tabs, etc. (this is our case - space separation);
— fixed width
- if we divide the data into equal (by columns) parts.
5. In the window Text Wizard (parsing) - step 2 of 3(Fig. 4) choose separator character — space . If the desired character is not among the proposed set, check the box another and enter the required character in the field. In this case, a sample partition will be shown in the lower part of the window.
6. By pressing the Next button, we go to the following dialog box: Text Wizard (parsing) - step 3 of 3(Fig.5), which allows you to set the data format for each new column. Button More opens a dialog where you can set additional settings for numeric data.
You can exclude an individual column when splitting. To do this, select it in the parsing sample and enable the option Skip column.
7. After setting all the required parameters, press the button Ready, as a result of which the contents of the selected range will be divided into three columns (Fig. 6).
When working with Excel spreadsheets, sometimes you need to split a certain cell into two parts. But, it is not as simple as it seems at first glance. Let's figure out how to split a cell into two parts in Microsoft Excel, and how to split it diagonally.
It should be noted right away that cells in Microsoft Excel are primary structural elements, and they cannot be divided into smaller parts if they have not been merged before. But what if we, for example, need to create a complex table header, one of the sections of which is divided into two subsections? In this case, you can apply small tricks.
Method 1: Merge Cells
In order for certain cells to appear split, other table cells must be merged.
As you can see, despite the fact that we did not divide anything, but, on the contrary, connected it, an illusion of a divided cell is created.
Method 2: split merged cells
If we need to split a cell not in the header, but in the middle of the table, then in this case, it is easier to merge all the cells of two adjacent columns, and only then split the desired cell.
Thus, we got a split cell. But, you need to take into account that Excel perceives a divided cell in this way as a single element.
Method 3: Split Diagonally by Formatting
But, even an ordinary cell can be divided diagonally.
After that, the cell will be divided by a diagonal slash. But, you need to take into account that Excel perceives a divided cell in this way as a single element.
Method 4: Diagonal Dividing Through Shape Insertion
The following method is suitable for dividing a cell diagonally only if it is large, or created by merging several cells.
As you can see, despite the fact that in Microsoft Excel there are no standard ways to split the primary cell into parts, using several methods you can achieve the desired result.
When working in an Excel spreadsheet, the user has various tasks, but even a seemingly simple action in it is not as easy as it seems. So, many people experience problems when trying to split a cell in Excel into two. This article will talk about just that.
How to split a cell into two
Roughly speaking, there are no special tools for splitting cells into two in the program, so you have to use adjacent ones. Further in the text, four methods will be proposed, which can be divided into two categories: those that involve dividing the cell diagonally, and vertically or horizontally. Without dwelling on the nuances for a long time, let's go directly to the methods of how to split a cell into two in Excel.
Method 1: through merging cells
As mentioned earlier, to split a cell into two, you need to resort to some tricks. This is so because the cell is the primary building block and cannot be split in two. Because of this, you can do the opposite: merge the cells in the table in such a way that the visually necessary ones are separated.
Immediately after this, you will notice that all two columns of cells below the merged ones will appear to be separated, although we did not perform any action on them. To see this more clearly, it is recommended to highlight the borders of the cells. To do this, follow these steps:
By the way, if you want to split a cell into two in Excel horizontally, then you just need to merge two cells to the right or left of it. In this case, the execution instructions will be the same.
This was the first way to split a cell into two. If you need to make, for example, a table header, then it will come in handy more than ever, but for other purposes it is better to use other methods. So let's jump right into them.
Method 2: split merged cells
And now it will be told how to split a cell in Excel into two for real. Of course, for this it will be necessary to initially resort to some actions, and to be more precise, to merge two adjacent columns. For this:
Now instead of two columns you have one. It is in it that you can split the cell into two. To do this, you need:
Now instead of one cell you have two. By the way, if you are thinking about how to split cell data in Excel into two, then, unfortunately, there are no special tools for this. You will need to use the usual "Copy" / "Paste". And it is better to first build the table structure and only then enter values into it.
Method 3: Split Diagonally with Formatting
We have already established that in Excel it is impossible to divide the value of a cell into two, you will have to enter the information after, however, if you divide it diagonally, then no preliminary actions need to be taken. And all this is done by formatting:
After that, the cell will be split into two, and you can enter your values into it.
Method 4: Diagonal Divide by Inserting a Shape
You can also split a cell diagonally using the Shapes tool. However, keep in mind that this is the best option only if your cell is formed by combining several.
After that, the cell will be split into two diagonally.
Instead of a conclusion
So we figured out what to do to separate cells in Excel. It is worth noting that it is impossible to split a cell with values into two in Excell using a special tool, this is done after a simple data movement. Otherwise, the task does not require much knowledge in working with a spreadsheet editor, especially if you have step-by-step instructions on how to do it at hand.
In this article, you will find several ways to split cells or entire columns in Excel 2010 and 2013. The examples and screenshots provided 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 numerical values. This lesson 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 better set up the filter, sorting, or for a more detailed analysis.
Splitting Cells in Excel Using the Column Text 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 delimiters, 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 Participant(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(A country), Arrival Date(Expected date of arrival) and Status(Status).
Note: If you don't have columns immediately following what you want to split, then this step is unnecessary 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 divide the data.
Advice: 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).
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 come before the name of this product:
Here's what you need to do to split such a column into two:
Splitting 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 File(File) > Options(Settings) > Advanced(Optional) > Automatically Flash Fill(Automatically perform instant filling).
Now let's see how you can automatically split 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 certain 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(LEFT SYMBOL), 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)
=LEFT(A2,SEARCH(" ",A2,1)-1) - Retrieve the last name (Last name column):
RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
=RIGHT(A2,LONG(A2)-SEARCH(" ",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,)
=SEARCH(search_text, search_text, [start_position])
As arguments, you must specify: what to find, where to search, 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.
Comment: 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)
LEFT(LEFT) and RIGHT(RIGHT) - returns the left or right part of the text from the given cell, respectively. Formula syntax:
LEFT(text)
=LEFT(text,[number_of_characters])
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)
=LEFT(A2,SEARCH(" ",A2)-1)
LEN(DLSTR) - counts the length of the string, that is, the number of characters in a given cell. Formula syntax:
LEN(text)
=DLSTR(text)
The following formula counts the number of characters in a cell A2:
LEN(A2)
=LSTR(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 split contain a middle name or only one middle initial in the middle.
- Extracting the name:
LEFT(A2,FIND(" ",A2,1)-1)
- We extract the middle name:
- Extracting the last name:
Function MID(PSTR) - extracts part of a text string (that is, a given number of characters). Syntax:
MID(text,start_num,num_chars)
=MID(text,start_position,number_of_characters)
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:
- Extracting the name:
LEFT(A2,FIND(" ",A2,1)-1)
=LEFT(A2,FIND(" ",A2,1)-1) - Extracting the last name:
MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
=MID(A2;FIND(" ";A2;1)+1;FIND(" ";A2;FIND(" ";A2;1)+1)-(FIND(" ";A2;1)+1) ) - Retrieving the suffix:
RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))
=RIGHT(A2,LONG(A2)-FIND(" ",A2,FIND(" ",A2,1)+1))
Example 4
And here are the formulas that allow you to split names with a surname in front and a comma separated from the name, and a patronymic at the end:
- Extracting the name:
MID(A2,SEARCH(" ",A2,1)+1,FIND(" ",A2,FIND(" ",A2,1)+1)-(FIND(" ",A2,1)+1))
=MID(A2;SEARCH(" ";A2;1)+1;FIND(" ";A2;FIND(" ";A2;1)+1)-(FIND(" ";A2;1)+1) ) - We extract the middle name:
RIGHT(A2,LEN(A2)- FIND(" ",A2,FIND(" ",A2,1)+1))
=RIGHT(A2,LONG(A2)-FIND(" ",A2,FIND(" ",A2,1)+1)) - Extracting the last name:
LEFT(A2,FIND(" ",A2,1)-2)
=LEFT(A2,FIND(" ",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:
- Retrieve the state name:
LEFT(A2,SEARCH(",",A2)-1)
=LEFT(A2,SEARCH(",";A2)-1) - Extracting the state abbreviation:
MID(A2,SEARCH(",",A2)+2,SEARCH(",",A2,SEARCH(",",A2)+2)-SEARCH(",",A2)-2)
=MID(A2;SEARCH(",";A2)+2;SEARCH(",";A2;SEARCH(",";A2)+2)-SEARCH(",";A2)-2) - Retrieve the state capital:
RIGHT(A2,LEN(A2)-(SEARCH(",",A2,SEARCH(",",A2)+1)+1))
=RIGHT(A2,LONG(A2)-(SEARCH(",";A2;SEARCH(",";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:
Nothing more convenient than a tabular form of information presentation, over the years of the development of office culture, mankind has not come up with. Employees are well aware of the spreadsheet editor Excel, which is included in all versions of Microsoft Office. Rich opportunities for creating and editing tables make it indispensable for compiling various forms of reports and organizing data.
Today we will look at how to split a cell into two in Excel - horizontally, vertically and diagonally. It would seem a simple question, but it is he who often baffles many users.
Excel sheet
First, let's talk a little about a standard Excel sheet. The main window of the program looks like a field of identical rectangular cells. Columns are designated by letters of the Latin alphabet from left to right, starting with one and ending with combinations of three. Lines are numbered sequentially from top to bottom with Arabic numerals. Thus, each cell gets its own unique alphanumeric address on the sheet.
In the process of creating a simple document, you will combine cells, achieving their desired location, and operate with various markup methods to define the boundaries of the created table for convenient presentation of data. In general terms, this lesson is similar to marking a notebook sheet in a box, with the only difference being that everything is done on the monitor.
Sheet sizes, of course, have physical limitations, but it is difficult to imagine a task that goes beyond them. If you switch to the language of numbers, then you have 1,048,576 rows and 16,384 columns at your disposal, resulting in a field of more than 17 million cells.
Excel cell
So, we already understood that the sheet consists of cells, and came close to our main question of how to divide a cell into two parts in Excel. You will be surprised, but this is impossible. A cell is the smallest component of a table and cannot be divided into parts. With this statement, "Microsoft" "pleases" us immediately on the main technical support page. Yes, you can not separate, but you can combine them in any order. And seventeen million cells will be enough even for the most demanding user. Therefore, in the next section, we will analyze in detail how to split a cell into two in Excel, or, to put it literally, how to make a cell look like it is divided into two halves.
Split Excel Cells
Office Help and the Microsoft support site give us some guidance on what to do and how to do it. In Excel, you can split a cell into two vertically or horizontally. A rarer case is diagonal division.
Consider what actions are necessary in all three cases:
How to split a cell vertically in Excel? To create a vertical division, you need to merge the cells in two adjacent columns at the top and bottom. Thus, the cell in which you want to set the vertical split will consist of two cells lying in a row on the same line. The cells above and below will also consist of two, but only merged. The required width of the resulting column can be set by adjusting the upper letter field.
How to split a cell horizontally in Excel? For horizontal division, the actions will be similar to those described above, with the exception that it will be necessary to combine not columns, but rows. And we will do this to the right and left of the cell in which we want to get a horizontal division. Accordingly, we will then adjust not the width, but the height using the left digital field.
How to split a cell diagonally in Excel? At first glance, there is nothing difficult in this task. In the "Borders" menu, you can find a diagonal line that allows you to divide the cell. But we divide it not for the sake of division, but in order to introduce some information into both halves. When separated by a simple line, the cell does not perceive it as a text boundary, and it will cross it. The tools we need are located in the "Insert" menu. We are looking for the “Shapes” icon here and open the drop-down list. "Line" will give us a diagonal division, and "Inscription" will allow us to enter text in both halves of the divided cell and position it relative to the dividing line as needed.
Finally
After reading this material in full, you learned how to divide a cell into two halves in Excel in all available ways. We hope this will help make your tables better and more informative.
