How to split data in an excel cell. How to divide full name in Excel

All tips

msoffice-prowork

Many people know that in order to combine text in two cells it is enough to use the CONCATENATE function, however, what if you need not to combine, but rather to separate the text in a cell? If the number of characters that needs to be separated is known (it does not matter on the right or 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 you need to split a cell in which the number of characters that need to be separated is not known, but only how many parts need to be obtained as a result of the operation is known. The simplest example of such a situation may be the need to separate the last name, first name and patronymic from the cell in which the person’s full name is entered. Everyone’s last name is different, so it’s not possible to know in advance the number of characters that need to be separated.

For your consideration, we will present two ways to divide 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 method is super fast.

In fact, there is a built-in ability to quickly split text in a cell if there is or are delimiters present (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, a semicolon, a comma, etc.

To do this, you need to select the cells with the text that needs to be divided 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 selecting the space separator, you can safely press “Finish”)

And here is, in fact, the result.

The second way is using formulas.

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

In addition, since there is no separator 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 knowingly larger number of characters as the argument "number of characters" of the PSTR function, for example, 100.

Let's look at the above with an example. First, for a better understanding, we will break down the formulas and thus divide the entire process into two stages.

In order to determine the number of characters that need to be highlighted in a line, it is necessary to determine the positions of the delimiters (in our case, spaces); they will be one less than the words in the cell.

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

Here, intermediate values obtained using the SEARCH function are used as arguments. 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 located 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.

Buy products

We previously considered the possibility using the example of dividing a full name into its component parts. To do this, we used the Excel Text by Columns tool.

Undoubtedly, this is a very important and useful tool in Excel that can greatly simplify many tasks. But this method has a slight drawback. If, for example, you are constantly sent data in a certain form, and you constantly need to divide it, then this takes a certain time, in addition, if the data was sent to you again, then you will need to do all the operations again.

If we look at the example of dividing a full name, then it will be possible to divide the text using Excel text formulas, using the and function, which we discussed in previous articles. In this case, you just need to insert data into a specific column, and the formulas will automatically split the text as needed. Let's start looking at this example.

We have a column with a list of full names, our task is to place the last name, first name and patronymic in separate columns.

Let's try to describe the action plan in great detail and divide the solution to the problem into several stages.

First of all, we’ll add auxiliary columns for intermediate calculations to make it clearer for you, and at the end we’ll combine all the formulas into one.

So, let's add columns position 1st and 2nd spaces. Using the FIND function, as we already discussed in the previous article, we will find the position of the first space. To do this, in cell “H2” we write the formula

FIND(" ";A2;1)

Now we need to find the sequence number of the second space. The formula will be the same, but with a slight difference. If we write the same formula, the function will find us the first space, but we need the second space. This means that it is necessary to change the third argument in the FIND function - the starting position - that is, the position from which the function will search for the searched text. We see that the second space is in any case after the first space, and we have already found the position of the first space, which means by adding 1 to the position of the first space we will tell the FIND function to look for the space starting from the first letter after the first space. The function will look like this:

Let's start dividing the first part of the text - Surnames

To do this, we will use the function, let me remind you of the syntax of this function:

PSTR(text; start_position; number_characters), where

text is a full name, in our example it is cell A2;
start_position- in our case it is 1, that is, starting from the first letter;
number_characters - we see that the surname consists of all characters, starting from the first letter and up to the 1st space. And we already know the position of the first space. This will be the number of characters minus 1 character of the space itself.

The formula will look like this:

PSTR(A2 ;1 ;H2-1 )

Let's start dividing the second part of the text - Name

Again we use the function =PSTR(text; start_position; number_characters), where

text is the same full name text, in our example it is cell A2;
start_position- in our case, the Name begins with the first letter after the first space, knowing the position of this space we get H2+1;
number_characters - the number of characters, that is, the number of letters in the name. We see that our name is between two spaces, the positions of which we know. If we subtract the position of the first space from the position of the second space, we get the difference, which will be equal to the number of characters in the name, that is, I2-H2

We get the final formula:

PSTR(A2 ;H2+1 ;I2-H2 )

Let's start dividing the third part of the text - Patronymic

And again the function =PSTR(text; start_position; number_characters), where

the text is the same full name text, in our example it is cell A2;
start_position- Our patronymic is located after the 2nd space, which means the initial position will be equal to the position of the second space plus one sign or I2+1;
number_characters - in our case there are no signs after the Patronymic, so we can simply take any number, the main thing is that it is greater than the possible number of characters in the Patronymic, I took a number with a large margin - 50

We get the function

PSTR(A2 ;I2+1 ;50 )

Next, select all three cells and drag the formulas down and get the result we need. You can finish here, or you can write intermediate calculations of the position of spaces into the text division formulas themselves. It's very easy to do. We see that the calculation for the first space is in cell H2 - FIND(" ";A2;1) , and the calculation for the second space is in cell I2 - FIND(" ";A2;H2 +1) . We see that in the formula of cell I2 there is H2, we change it to the formula itself and we get a nested formula in cell I2

We look at the first formula for selecting the Last Name and see where H2 or I2 occurs here and change them to the formulas in these cells, similarly with the First Name and Last Name

Last name =PSTR(A2,1,H2 -1) we get =PSTR(A2,1,FIND(" ",A2,1) -1)
Name =PSTR(A2;H2 +1;I2 -H2) we get =PSTR(A2;FIND(" ";A2;1) +1;
FIND(" ";A2;FIND(" ";A2;1)+1)-FIND(" ";A2;1) )
Patronymic =PSTR(A2;I2 +1;50) we get =PSTR(A2; FIND(" ";A2;FIND(" ";A2;1)+1)+1;50)

Now intermediate position calculations with a space can be safely removed. This is one of the techniques when, for simplicity, intermediate data are first looked for, and then the function is nested one inside the other. Agree, if you write such a large formula at once, it’s easy to get confused and make mistakes.

We hope that this example clearly showed you how useful Excel text functions are for working with text and how they allow you to divide text automatically using formulas of the same type of data. If you liked the article, we will be grateful for clicking on +1 and I like it. Subscribe and join our VKontakte group.

Example 2: How to split text into columns in Excel using a formula

Let's consider the second example, which is also very often encountered in practice. The example is similar to the previous one, but there is much more data that needs to be divided. In this example, I will show a technique that will allow you to quickly resolve the issue and not get confused.

Let's say we have a list of numbers separated by commas, we need to split the text so that each number is in a separate cell (instead of commas, these can be any other characters, including spaces). That is, we need to break the text into words.

Let us remind you that this problem can be solved manually (without formulas) using the method we have already considered. In our case, we need to do this using formulas.

First we need to find a common division by which we will split the text. In our case, this is a comma, but for example, in the first task we divided the full name and the separator was a space. Our second example is more universal (more convenient when there is a large amount of data), so for example we could conveniently divide not only the full name into separate cells, but the whole sentence - each word into a separate cell. Actually, this question came up in the comments, so it was decided to supplement this article.

For convenience, we will indicate this separator in the adjacent column so that we do not have to write it in the formula, but simply refer to the cell. This will also allow us to use the file to solve other problems by simply changing the separator in the cells.

Now the main essence of the technique.

Step 1. In the auxiliary column we find the position of the first separator using the FIND function. I will not describe the function in detail, since we have already looked at it earlier. Let's write the formula in D1 and extend it down to all lines

FIND(B1;A1;1 )

That is, we are looking for a comma in the text, starting from position 1

FIND($B1 ;$A1;D1+1 )

First, let's fix the column of the desired value and text so that when dragging the formula to the right, the cell references do not move. To do this, you need to write a dollar before the column B and A - either manually, or select A1 and B1, press the F4 key three times, after which the links will become absolute, not relative.

Secondly: we will calculate the third argument - the beginning of the position as the position of the previous separator (we found it above) plus 1, that is D1+1 since we know that the second delimiter is exactly after the first delimiter and we do not need to take it into account.

Let's write down the formula and drag it down.

Step 3. We find the positions of all other separators. To do this, extend the formula for finding the second separator (step 2) to the right by the number of cells equal to the total number of separately divided values with a small margin. Let's get all the separator positions. Where the #Value error means that the values have run out and the formula no longer finds delimiters. We get the following

Step 4. We separate the first number from the text using the PSTR function.

PSTR(A1;1 ;D1-1 )

Our starting position is 1, we calculate the number of characters as the position of the first separator minus 1: D1-1 drag the formula down

Step 5. We also find the second word using the PSTR function in cell P1

PSTR($A1;D1+1;E1-D1-1)

The starting position of the second number begins after the first comma. We have the position of the first comma in cell D1, add one and get the starting position of our second number.

The number of characters is the difference between the position of the third separator and the second and minus one character, that is, E1-D1-1

Let's fix column A of the source text so that it does not move when dragging the formula to the right.

Step 6. Let's stretch the formula obtained in step 5 to the right and down and get text in separate cells.

Step 7 In principle, our problem has already been solved, but for beauty, in the same cell P1 we will write a formula that catches an error, replacing it with an empty value. You can also group and collapse auxiliary columns so that they do not get in the way. We get the final solution to the problem

IFERROR(PSTR($A1,D1+1,E1-D1-1); "")

Note. We did the first position of the separator and the first division of the word differently from the others and because of this we could only extend the formula from the second values. While writing the problem, I noticed that the problem could be simplified. To do this, in column C it was necessary to enter 0 for the value of the first separator. After this we find the value of the first separator

FIND($B1,$A1,C1+1)

and the first text as

PSTR($A1;C1+1;D1-C1-1)

After this, you can immediately extend the formula to the remaining values. I leave this option as an example for downloading. In principle, the file can be used as a template. Insert data into column “A”, specify a separator in column “B”, stretch the formulas to the required number of cells and get the result.

Attention! The comments noted that since we do not have a delimiter at the end of the text, we do not count the number of characters from the last delimiter to the end of the line, so the last delimited text is missing. To solve the issue, you can either, in the first step, add an auxiliary column next to the source text, where you can combine this text with a separator. This way we will have a separator at the end of the text, which means our formulas will calculate its position and everything will work.

Or the second solution is in step 3, when we compose a formula for calculating the positions of the separators and supplement it. Check, if there is an error, then indicate a obviously large number, for example 1000.

IFERROR(FIND($B1,$A1,C1+1),1000)

Over the years of development of office culture, humanity has not come up with anything more convenient than a tabular form of presenting information. Employees are very familiar with the Excel spreadsheet editor included in all versions of Microsoft Office. Rich possibilities for creating and editing tables make it indispensable for creating various forms of reports and organizing data.

Today we will look at how to divide a cell into two in Excel - horizontally, vertically and diagonally. It might seem like a simple question, but it is one that often confuses many users.

Excel sheet

First, let's talk a little about a standard Excel worksheet. The main program window 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 using Arabic numerals. This way, each cell gets its own unique alphanumeric address on the sheet.

In the process of creating a simple document, you will combine cells to achieve their desired location, and operate with various markup methods to determine the boundaries of the table being created for convenient presentation of data. In general terms, this activity is similar to marking a notebook sheet in a box, with the only difference being that everything is done on the monitor.

The dimensions of the sheet, of course, have physical limitations, but it is difficult to imagine a task that goes beyond them. If we switch to the language of numbers, then you have 1,048,576 rows and 16,384 columns, creating a field of more than 17 million cells.

Excel cell

So, we have already understood that the sheet consists of cells, and we have come 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 minimum component of a table and cannot be divided into parts. With this statement, Microsoft “delights” us immediately on the main technical support page. Yes, you can’t separate them, but you can combine them in any order you like. And seventeen million cells are enough for even the most demanding user. Therefore, in the next section we will look in detail at how to split a cell into two in Excel or, literally speaking, how to make a cell look like it is divided into two halves.

Splitting Excel cells

Office Help and the Microsoft Support site give us some guidance on what to do and how to do this. In Excel, you can split a cell into two vertically or horizontally. A rarer case is diagonal division.

Let's consider what actions are necessary in all three cases:

How to split a cell into two 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 vertical division will consist of two cells lying in a row on the same row. The cells above and below will also consist of two, but only combined. The required width of the resulting column can be set by adjusting the upper letter margin.

How to split a cell into two horizontally in Excel? For horizontal division, the steps will be similar to those described above, with the exception that you will need to merge 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 into two diagonally in Excel? At first glance, there is nothing complicated in this task. In the Borders menu, you can find a diagonal line that allows you to divide a cell. But we divide it not for the sake of division, but in order to introduce some information into both halves. When divided by a simple line, the cell does not perceive it as a text boundary, and it will intersect it. The tools we need are located in the “Insert” menu. We look 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 into both halves of the divided cell and position it relative to the dividing line as necessary.

Finally

After reading this material in its entirety, you learned how to divide a cell into two halves in Excel using all available methods. We hope this will help make your tables better and more informative.

It is often necessary to optimize the data structure after importing into Excel. Some different values fall into the same cell, forming an entire row as one value. The question arises: how to split a row into cells in Excel. The program has different search functions: some search by cells, others search by the contents of the cells. After all, searching for a text string contained in a cell is also a common need for Excel users. We will use them to separate lines.

How to split text into two Excel cells

Let's say data was imported into an Excel sheet from another program. Due to incompatible data structure during import, some values from different categories were entered into one cell. It is necessary to separate integer numeric values from this cell. An example of such incorrectly imported data is shown in the figure below:

First, let's define a pattern by which we can determine that data is from different categories, despite the fact that they are in the same row. In our case, we are only interested in the numbers that are outside the square brackets. How can you quickly select integers from strings and place them in separate cells? An effective solution is a flexible formula based on text functions.

In cell B3, enter the following formula:

Now copy this formula along the entire column:

Selecting numbers from strings into separate cells.

Description of the formula for dividing text into cells:

The PSTR function returns a text value containing a certain number of characters in a string. Function arguments:

The first argument is a reference to the cell with the source text.
The second argument is the position of the first character at which the split string should begin.
The last argument is the number of characters the split string should contain.

Everything is clear with the first argument of PSTR - this is a link to cell A3. We calculate the second argument using the FIND("]";A3)+2 function. It returns the next character number of the first closing square bracket in the string. And to this number we add another number 2, since we need the character number after the space behind the square bracket. In the last argument, the function calculates how many characters the split string will contain after splitting, taking into account the position of the square bracket.

Note! That in our example, all the original and split lines have different lengths and different numbers of characters. That is why we called this formula flexible at the beginning of the article. It is suitable for any conditions when solving such problems. What gives it flexibility is a complex combination of FIND functions. The user of the formula just needs to determine the pattern and indicate them in the function parameters: these will be square brackets or other separating characters. For example, these could be spaces if you need to divide a line into words, etc.

In this example, the FIND function takes its second argument to determine the position relative to the first closing parenthesis. And in the third argument, the same function calculates the position of the text we need in the line relative to the second opening square bracket. The calculation in the third argument is more complex and involves subtracting one larger length of text from a smaller one. And to take into account 2 more spaces, you need to subtract the number 3. As a result, we get the correct number of characters in the divided line. Using such a flexible formula, you can select different lengths of split text from different lengths of source strings.

How to split text into columns into two, three or more cells in Excel 2007. Usually, as an example, they use a breakdown of cells with a full name so that the data is separate.

How to split text into columns in Excel

At work, I had to divide the time in the cells of an excel table, example: 0:00-15:00. And between the values the separator is a semicolon 0:00-15:00;16:00-17:00;22:00-24:00. This data is in the table by day, in cells there is a different number of values between the separator. It was necessary to divide the time for each day, put the time in a column under the date, divide the time into the beginning and end, calculate the difference and add everything together...

Stage one. Divide time in cells

Select a range of cells, on the ribbon click Data-Text by Columns, the Text Wizard will appear.

We don’t change anything, if your switch is in the position: with separator, click next.

Uncheck the "tab" checkbox and check the "other" checkbox

and enter a semicolon [; ]. Click either further, although there’s nothing special to see there, or rather, click ready. The question will be asked: replace the contents of the cells? The answer is yes!

We were able break a cell by as many values as there were between [ ; ]. Let's add empty lines under the cell with the date, in the number of cells to the right of column C.

Two cells, two rows. Select cells, copy,

and under the first cell, right-click “Paste Special” - “Transpose”.

The data will be transferred from row to column, let's do the same for the remaining cells.

Happened split text in a cell, format by day, in a column.

Stage two. How to divide a cell into a start and end time in Excel

Let's get the time difference by using the text wizard again. Let's continue split text in cells for start and end times. Here the separator is the minus sign.

Let's move the column out of our table by copying and pasting the cells next to each other. We work with the wizard: Data-Text by columns, the first step does not change anything, in the second step in the other [ - ] window we put a minus sign, click ready.

Stage three

We put the formula: end time minus start time,

cell format All formats [h]:mm,

Let's drag the formula down to the end. We got the time in hours. In the next cell we write: =K4*60 Enter. Let's get the time in minutes.

True, with seconds... I couldn’t find a better way than copying a column, pasting it into Notepad, pressing CTRL+H (Find and Replace), in find wrote:00

and left the replacement blank...

I clicked replace all... The text without:00 was copied from notepad and pasted into the table.

I did it easier with the number of hours, copied it into a notepad and pasted it into the table. One problem: 24 hours are worth 24:00:00.

Not a problem, since we know how to split a cell in excel, then you can use Copy&Paste here, but with a trick - in the cell before 24:00 put an apostrophe ‘ -single quote “24:00”

and paste it everywhere. You can only add minutes with an autosum; we get the sum of hours by dividing the sum of minutes by 60, and the number of days we divide the resulting number of hours by 24....

In my example, something out of 31 days worked for 28 days, out of 744 hours -673 hours and out of 44640 minutes -40411 minutes... It doesn’t matter what it is, the main thing is that in this example you learned how to do it in Excel split text into columns in a cell, how can you use a notepad in a non-standard way and change the format of the cells by adding an apostrophe to them.

This video shows in detail how to split text into columns:

We recommend watching the video in full screen mode; in the quality settings, select 1080 HD, Do not forget subscribe to YouTube channel, there you will find a lot of interesting videos that come out quite often. Enjoy watching!