How to Excel to split a row of a cell. Combining and splitting data in cells in Excel with formatting. Video: Formulas in Excel

A sheet in Microsoft Excel is itself a large table consisting of cells that are assigned a specific address (a number corresponding to a row and a letter corresponding to a column). At this address, you can access the data, that is, to use it in formulas. Accordingly, it will not work to divide the cell directly, as we say in Word. Since the A5 address will not become A / 1-5 and A / 2-5. So what can be done?

If you have not yet typed a table and the data is not entered, then initially think over everything well, and then simply combine certain cells, leaving them, for example, separated only in the header or in some row.

But what to do if the table is ready, let's tell you in this article. Let's figure out how to divide a cell in Excel into two or more horizontally and vertically, as well as diagonally.

For example, we will have such a table. In it, after the Name, you also need to indicate the Manufacturer.

Since new columns are added to the left of the current selection, I select any value in column C and insert the columns on the Home tab.

So, after the Name, another vertical column has appeared, which I will call Manufacturer.

Since fruits can be brought from different countries, you need to make several opposite each in the added column from one cell.

Let's start with Apple. Select the block immediately under the given name, right-click and select "Paste" from the context menu.

Mark with a marker in the box "line" and click "OK".

An additional line will be added in the place we need. In the same way, more lines are added if there are more Producers.

Now let's merge the cells in those columns where the data does not change. In the example, these are Number, Title and Quantity. First you need to select them, then click on the "Combine" button and select "Cells".

You can read about the different ways of combining cells in Excel in the corresponding article.

Fill in the Manufacturer and Price fields with the data.

As you can see, by adding rows and merging certain cells, we achieved the fact that in the desired column it turned out to split them horizontally into several rows. Thus, Yabloko has several different Producers and a different Price.

You can do the same with Pear. Add lines, combine where Number and Name, and only two rows in the Quantity column (since the value will be the same). Then we enter the data and get a sort of divided cell in the Manufacturer and Price field.

Please note that in the combined blocks, the address of the upper left must be specified in the formula. For example, from D2: D3 it will be D2, and from B4: B6 it will be B4.

Now let's look at how to split one cell into 2 or more vertically. This is necessary if you want to add several subtitles to the column name, and only then fill them with data.

For example, let's divide the Quantity column. From the second row, select any non-divided cell, it can be C2 or E2. We call the context menu and insert an additional line.

Use the corresponding button to merge D1 and E1, placing the value in the center.

In such a tricky way, we split the cell in the column under the word Quantity and gave them the names Delivered and Sold. Where Sold, I still have previously merged cells with a value, if it does not fit, you just need to cancel it by selecting the appropriate item.

As a result, we got a table in which, in the right places, the cells are divided into several rows or columns. And we did this not by dividing, but by adding horizontal and vertical rows, with the subsequent unification of some blocks.

To split it diagonally, select the cell, open the context menu by right-clicking and press "Format".

The selected block will be divided.

You can do it differently. At the top, open "Insert", click "Illustrations" - "Shapes" and select a straight line from them.

In the desired cell, draw it diagonally from one corner to another. If, as in the example, it is of the wrong color, then on the tab "Drawing Tools" select the one you want or from the preset styles, or by clicking on "Shape Outline".

Excel allows you to split text in column cells into multiple substrings and arrange them in separate columns. In this case, a special separator is used for splitting, which is selected by the user for each specific case.

Let's take a look at the data of a CSV file as an example. Each substring in it is separated by a semicolon and enclosed in double quotes. Paste them anywhere in your workbook:

"Full name"; "Date of birth"; "Gender"; "City of residence"; "Profession"
"Ivanov Ivan Ivanovich"; "03/27/1985"; "Male"; "Voronezh"; "Electrician"
"Petrov Petr Petrovich"; "03/12/1976"; "Male"; "Moscow"; "Geologist"
"Elizaveta Fedorovna Kukhtina"; "12/11/1994"; "Female"; "Kazan"; "Doctor"

Before dividing the data, you need to select the desired range, and then click on the "Text by Columns" icon in the "Data Tools" area on the ribbon of the "Data" tab.

The text distribution wizard window will be displayed on the screen:

The whole process consists of three steps.

At the first stage, you need to select the data format for separation, namely, indicate whether the text contains separators, or each future column has its own fixed width, i.e. the length of the text. The last option will not be considered, since it is no different from the first, except for the next step, where you need to independently select the field width on the ruler.

Select Delimited and click Next.

The next step the delimiter character is selected. It can be any of the characters, including letters and numbers.

Please note that if the delimiters are in a row, then each of them forms a new column, i.e. 2 such symbols create 1 empty column, 3 - 2, etc. To get rid of this often necessary action, it is enough to check the box "Consider sequential separators as one".

A situation may arise where the substring for creating a column contains a delimiter character that is not. In such cases, the strings must be enclosed in single or double quotes (in our example of the source data, this is present), and in the wizard window you need to choose which line delimiter to use.

Note that separator characters and delimiters are removed from the content of the split data.

At any of the above steps, you can click Finish to enable Excel to complete the splitting of the text in the column cells on its own. But if you want to control the whole process, then continue to click "Next".

The final step is to assign formatting to new columns and indicate where to place them.

Of the possible settings, only a few formats (data types) are provided, and you can place the distributed text only on the current sheet.

msoffice-prowork

Many people know that in order to concatenate text in two cells, it is enough to use the CONCATENATE function, however, what if it is necessary not to concatenate, but rather to separate the text in a cell? If the number of characters to be separated is known (it does not matter to the right or to the left), then you can use the LEFT or RIGHT functions, depending on which side you need to select a certain number of characters.

However, what to do if it is necessary to divide a cell in which the number of characters to be separated is not known beforehand, but only how many parts need to be obtained as a result of the operation. The simplest example of such a situation may be the need to select from the cell in which the person's full name is entered, separately the last name, first name and patronymic. Everyone's surnames are different, so you won't be able to find out the number of characters that need to be separated in advance.

For your consideration, we will present two ways of separating text. One is very fast - for those who just need to split the text by replacing the existing one, and the second using formulas.

The first way is super fast.

In fact, there is a built-in ability to quickly split text in a cell if there is or is present a delimiter / separators (for example, a simple space or comma). Moreover, there can be several such separators, i.e. the text will be separated if the line contains either a space, or a semicolon or a comma, etc.

To do this, select the cells with the text that you want to split and use the "Text by Columns" command.

In the English version, this command sounds like "Text to Columns" of the "DATA" tab.

After simple prompts from the wizard (in fact, in our example, after choosing a separator - a space, you can safely press "Finish")

And here is, in fact, the result.

The second way is using formulas.

In such a situation, you need a combination of SEARCH and MID functions. To begin with, using the first one, we find a space between the words (between the last name and the first name and the first name and patronymic), and then we connect the second in order to select the required number of characters. Roughly speaking, the first function is to determine the number of characters, and the second is already divided.

In addition, since there is no separating character at the end of the line, the number of characters in the last word (in our case, patronymic) cannot be calculated, but this is not a problem, it is enough to specify a obviously larger number of characters as the argument "number of characters" of the MID function, for example, 100.

Let's consider the above with an example. First, for a better understanding, let's split the formulas and thus divide the whole process into two stages.

In order to determine the number of characters that must be selected in a line, it is necessary to determine the position of the separators (in our case, the space), there will be one less than words in the cell.

Since the information on the number of characters required is obtained, the next step is to use the MID function.

Here, as arguments, intermediate values obtained using the SEARCH function are used. For the last column, the number of characters is unknown, so a obviously larger number of characters was taken (in our case, 100).

Now let's try to combine intermediate calculations into one formula.

If the text in a cell needs to be divided into only two parts, then it is necessary to search for only one space (or another separator that is between words), and to divide into 4 or more parts, the formula will have to be complicated by searching for the 3rd, 4th, etc. separators.

Purchase Products

When working in an Excel spreadsheet processor, 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 two in Excel. This article will talk about just that.

How to split a cell in two

Roughly speaking, there are no special tools for dividing cells into two in the program, so you will have to use adjacent ones. Further in the text, four methods will be proposed, which can be divided into two categories: those that imply the division of 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 cell concatenation

As mentioned earlier, in order to split a cell into two, you need to resort to some tricks. This is because the cell is the primary building block and cannot be split in two. Because of this, you can do the opposite: combine the cells in the table in such a way that the visually needed ones are separated.

Have a clear idea of how the table will look in the end.
Select the two cells above the ones that are supposed to be separate.
On the toolbar, click the Merge and Center button. It is located in the Alignment category.

Immediately after that, you will notice that all two columns of cells below the merged will appear to be separated, although we did not perform any action on them. To see this more clearly, it is recommended to select the borders of the cells. To do this, follow these steps:

Select the range of cells that go into your table.
On the toolbar, click on the "Borders" button.
Select All Borders from the drop-down menu.

By the way, if you want to split a cell into two horizontally in Excel, then you just need to merge two cells to the right or left of it. In this case, the instructions for execution 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 as never before, but for other purposes it is better to use other methods. Therefore, we immediately turn to them.

Method 2: splitting merged cells

And now I will tell you how to split a cell in two in Excel for real. Of course, for this it will be necessary to initially resort to some actions, or, to be more precise, to combine two adjacent columns. For this:

Run the program.
While holding down the left mouse button on the names of the columns (Latin letters located above the first row of the table), select two columns.
While on the main tab of the application, click on the arrow next to the "Align" button.
From the drop-down menu that appears, select the "Combine by Rows" line.

Now, instead of two columns, you have one. It is in it that you can split a cell into two. To do this, you need:

Click on the cell that you want to split into two. It is important that it is in the column that you get after the merge.
Go to the "Home" tab.
In the Align toolset, click the Merge and Center button.
In the drop-down menu that appears, select the "Unmerge Cells" option.

Now, instead of one cell, you have two. By the way, if you are thinking of 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 structure of the table and only then enter the values into it.

Method 3: split diagonally using 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, but if you divide it diagonally, then you do not need to take any preliminary actions. And all this is done by formatting:

Open a workbook and go to the sheet where you want to split the cell.
Highlight it.
Then click the right mouse button.
In the context menu, select the "Format Cells" option.
A window will appear in which you need to go to the "Border" tab.
Here is the interface of the cell with the text "Label". In the lower right or left corner there are buttons, after pressing which the cell is split with a line diagonally from right to left or from left to right, respectively.
After that, you can also select the line type and its color.
After specifying all the required parameters, click OK.

After that, the cell will be split into two, and you can enter your values into it.

Method 4: split diagonally by inserting a shape

You can also split a cell diagonally using the Shapes tool. However, it should be borne in mind that this is the best option only if your cell is formed by combining several.

Select the desired cell.
On the "Insert" tab, click on the "Shapes" button.
Select the object of the same name from the drop-down list from the "Lines" category.
Hold down the left mouse button and draw a line from one corner to another.

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 simply moving the data. For the rest, the implementation of the task does not require a lot of knowledge in working with the spreadsheet editor, especially if you have step-by-step instructions on how to do this at hand.

Many people know that in order to combine text in two cells, it is enough to use the function, however, what if it is necessary not to combine, but rather to separate the text in a cell?

If the number of characters to be separated is known (it does not matter to the right or to the left), then you can use the functions or, depending on which side you need to select a certain number of characters.

For your consideration, we will present two ways of separating text. One is very fast - for those who just need to split the text by replacing the existing one, and the second using formulas.

The first way is super fast

In fact, MS Excel has a built-in ability to quickly split text in a cell if there is or is present a delimiter / separators (for example, a simple space or comma). Moreover, there can be several such separators, i.e. the text will be separated if the line contains either a space, or a semicolon or a comma, etc.

To do this, select the cells with the text that you want to separate and use the "Text by Columns" command.

In the English version of MS Excel, this command sounds like "Text to Columns" of the "DATA" tab.

After simple prompts from the wizard (in fact, in our example, after choosing a separator - a space, you can safely press "Finish")

And here is, in fact, the result.

The second way is using formulas

In such a situation, you need a combination of functions: and. To begin with, using the first one, we find a space between the words (between the last name and the first name and the first name and patronymic), and then we connect the second in order to select the required number of characters. Roughly speaking, the first function is to determine the number of characters, and the second is already divided.

In addition, since there is no separating character at the end of the line, the number of characters in the last word (in our case, patronymic) cannot be calculated, but this is not a problem, it is enough to specify a deliberately larger number of characters as the argument "num_chars" of the MID function, for example, 100 ...

Let's consider the above with an example. First, for a better understanding, let's split the formulas and thus divide the whole process into two stages.