Good afternoon, dear reader!
In the article I want to consider the question of how and in what ways it is possible to split the text in a cell that happens to be stuck together! If you often work with imported data in Excel, you periodically encounter problems such as unloaded data, incorrect data format, stuck words or values, and much more. In this case, Excel provides several options for normalizing data, and each of them has its own pros and cons.
You can parse the merged text into the necessary components using:
Text Parsing Wizard
Let's look at the simplest way to divide text in a cell, not by essence, but by execution. For example, this can be very clearly demonstrated by using your full name in one cell, which must be divided into separate columns for convenience and.
To complete the task, call the “Text Wizard (Parsing)” dialog box and separate the text in 3 steps:
- First, you need to select the data that needs to be divided, the next step is on the “Data” tab in the “Working with Data” section, click the “Text by Columns” icon and in the wizard dialog box that opens, specify the format of the working text. We choose 2 types of formats:
- General– does not make changes to the data, leaving them in their original form, there will be optimal choice in most cases;
- Text – this format, is mainly necessary for columns with numerical values, which the program must necessarily interpret as text. (For example, these are numbers with a thousand separator or a plastic card number);
- Date– this format is used for columns with dates; by the way, the format of the date itself can be selected in the drop-down list.
In the case when you use characters that are not similar to the standard ones assigned in the regional settings, you can use the “More details” button to correctly recognize them. 
Unlinking text using formulas
For this method, we will need the capabilities of combining the SEARCH and PSTR functions. With help, we will look for all the spaces that exist between words (for example, between the last name, first name and patronymic). Then we select the required number of characters for further separation.
And if with the first two words it is clear what and how to separate, then there is no separator for the last word, which means that you need to specify a conditionally large number of characters as an argument, like the “number_characters” argument for the PSTR function, for example, 100, 200 or more .
Now let’s look at the formation of a formula for separating text in a cell step by step:
- First, we need to find two spaces that separate our words, to find the first space we need the formula: =SEARCH(" ";B2;1), and for the second one: =SEARCH(" ";B2;C2+1) ;
- Secondly, we determine how many characters need to be highlighted in the line. Since we have already determined the positions of the separator, we will have one less character to separate. This means that we will use the PSTR function to remove words from the cell using the result of the previous formula as the “number of characters” argument. To determine the first word (Last Name), you need the formula: =PSTR(B2,1,SEARCH(" ";B2,1)), to determine the second value (Name): =PSTR(B2,SEARCH(" ",B2,1) +1;SEARCH(" ";B2;SEARCH(" ";B2;1)+1) -SEARCH(" ";B2;1)), and now let's define last value(Patronymic): =PSTR(B2;SEARCH(" ";B2;SEARCH(" ";B2;1)+1)+1;100).
As a result, we divided the full name into three words, which will allow us to work with them effectively.
If you divide the value in a cell into two parts, then you are looking for only one space (or other separator), but to divide more than 4 words, add the necessary separators to the formula.
Extracting words using macrosVBA
Let's look at two ways to split text in a cell:
- We pull out individual words by separator;
- Divide the text without spaces.
Method number 1.
Since you are interested in automatic text division, you need to write good function in VBA and implement it into the workbook. To begin, go to the “Developer” tab and select “ Visual Basic"or call this feature using Alt+F11. (more details in the article “”).
Create a new module in the “Insert” menu, select the “Module” item and transfer the code below into it:
Function Substring(Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) If n > 0 And n - 1<= UBound(x) Then Substring = x(n - 1) Else Substring = "" End If End Function
Function Substring (Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) If n > 0 And n - 1<= UBound (x ) Then Substring = x(n - 1) Else Substring = "" End If End Function |
This function has now appeared in the Function Wizard under User Defined. The syntax of the created function is very simple and looks like this:
SUBSTRING (_Txt_;_Delimeter_;_n_), where:
- – indicate the cell the text in which we will divide;
- – this is a character that serves as a separator (comma, dash, space, etc.);
- – put the number in the order of the fragment that we will extract.
Example:
Let's look at how it is possible to extract text based on various delimiter characters. For convenience and better automation, do not forget the meaning of the formula. 
Method number 2.
Sometimes there are options when, when downloading data from other sources, you can get a large text from words glued together (for example, Orlov Stepan Fedorovich), such text must be separated by spaces into its constituent parts.
A small function will help with this, which can automatically insert a “space” before all capital letters in the text. Using Visual Basic, create a new module as described in the first method and add the following code to it:
Function CutWords(Txt As Range) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len(Txt) If Mid(Txt, i, 1) Like "" And Mid(Txt, i + 1, 1) Like "" Then Out = Out & Mid(Txt, i, 1) & " " Else Out = Out & Mid(Txt, i, 1) End If Next i CutWords = Out End Function
Function CutWords (Txt As Range ) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len (Txt) If Mid (Txt , i , 1 ) Like "" And Mid (Txt , i + 1 , 1 ) Like "" Then |
Instructions
If the cell you want to split was created by combining several table cells, then the operation will be very simple. Start by selecting this cell - click on it with the mouse cursor. At the same time, the “Merge and Center” button located in the “Alignment” command group on the “Home” tab will be highlighted in the menu. Click this button or expand the drop-down list associated with it and select “Cancel". In both cases, the result will be the same - Excel will divide the cell into its constituent cells.
If you need to split a cell that is not a composite cell, you will have to perform a slightly larger number of operations. You need to make adjacent table cells composite, creating the impression that the desired cell is divided into several sections. If you need to split a cell horizontally, you will have to merge the cells in the row, and to split it vertically, you need to do this with the cells in the column.
Start by highlighting the elements you need in a row or column when creating a table. For example, if one cell in a column needs to be divided into three vertical sections, highlight the cells in three adjacent columns to the height of the table.
Merge the selected range row by row. On the Home tab, expand the Merge and Center button drop-down list from the Alignment group of commands and select Merge by Rows.
Change the width of the merged column - make it equal to the adjacent columns. To do this, select all the merged columns - in the example used there are three of them. Hover over the border between the headers of any two selected columns and drag it to the desired column width. The horizontal size of all selected columns will change synchronously.
Select the cell for which all these manipulations are performed. Once again, expand the Merge and Center button's drop-down list, but this time select the Unmerge Cells command. This is the last operation, after which the desired cell will be divided into a given number of parts. If necessary, divide into horizontal sections all the described actions must be performed with lines.
Sources:
- split cells in excel
- Split merged cell and fill each cell
You have been given the task: to create an accounting table for all workers of the enterprise using the Microsoft Office Excel spreadsheet editor. Before creating the table, you calculated the required number of columns and created it. But your boss demanded that you divide one column “Last Name First Name Patronymic” into 3 components. In order to split a cell as quickly as in MS Word, read the continuation of the article.
You will need
- Microsoft Office Excel software.
Instructions
In the “Text Wizard (parsing) - step 2 of 3” window, go to the group “The separator character is” - check the box next to the “space” item (Here you need to select the character that separates the words in the cell that we want to split. In our In this case, this character is a space). After completing these steps, click the "Done" button.
The settings window will close, view the result of the work. If the cell was divided incorrectly, undo the last actions (by pressing the keyboard shortcut Ctrl + I) and try this operation again.
As a result of the actions performed, you received a division of the cell into separate parts, based on the specified parameters.
Video on the topic
In our age of widespread computerization, the skills of a confident user of office suites are no longer a luxury, but a necessity. The volume of information to be processed is growing every day, and a simple calculator is not enough. Using a program such as Excel, we can easily process arbitrarily large volumes of tabular data. Let's consider the division operation using Excel (including mass division).
Instructions
In order to understand how Excel works, you should understand the following basic points:
data in Excel is a value that contains one ;
all operations performed in Excel are performed specifically on cell data;
cell coordinates look like:<буква><цифра>, Where<буква>is the designation of the matrix column, and<цифра>- designation of a matrix row (eg A1).
Let's say we have two columns of numbers that need to be divided between each other. First, let's learn how to divide one cell into another.
Left-click the cell in which you want to write the division result.
After the division sign, select the second cell (i.e., the one by which the division will be performed).
You can repeat the above steps as long as you like to form compound math calculations. You don't have to limit yourself to two cells. You can also use parentheses.
To do this, left-click on the cell with the result. Next, move the cursor to the bottom right of the cell (the cursor should change from a white cross to a thinner black one). Hold down the left mouse button and drag the cursor to the very bottom of the table, then release the button.
As a result, we processed the entire tablet, writing the formula only once.
Sources:
- like variables in excel
- how to split a cell diagonally and write in it
Working on a computer almost always involves typing. Sometimes you need to enter a formula into the command line to force the operating system to execute a specific command. To gain access to private documents, the user needs to specify an account, which is a complex password that consists of various numbers, letters and other symbols. Elements with text are present in files, services, and programs. The main work with texts is carried out in text editors, where you simply need to have information on how to separate words from the text.
Here we will consider,How to split text in an Excel cell into multiple cells.For example, a sentence from the first cell should be spread according to words into several other cells, full name, date, etc.
For example, we have a list with last names and first names in one column. We need to write last names in one column and first names in another column. For example, to determine the gender of people on a list. Read more about this in the article “In Excel, determine gender by name.”
Select the cells in which the text needs to be divided or the entire column.
Go to the “Data” tab in the “Working with Data” section and select the “Text by Columns” function.In the “Text Wizard” window that appears, in the “Source data format” line, indicate “With delimiters.” Click the “Next” button.
Here we indicate in the line “The separator character is” - “space”. We put a “space” because in our list the first and last names are written separated by a space. If the first and last name (other words) were written separated by commas, then we would indicate the delimiter “comma”. 
In this window, in the “Data Parsing Sample” section, you can immediately see what columns we will get.Click the “Next” button.
Here, in the “Place in:” section, we indicate the range of columns into which the new data will fit. 
Attention!
The columns in this range must be empty, otherwise the information in them will be erased and replaced with new ones. If there are no empty columns, you can pre-insert columns. How to do this, see the article “How to add a row, column in Excel”.
You can specify the format of the Column Cells. For example, if you divide a column into days and months, year, then you can specify the “Column Data Format” - “date”.
Click the “Finish” button. It turned out like this.
This way you can split text from one cell into more columns.For example, here we divided the date separately into columns - day, month, year.
You can divide the cell diagonally and write text in each part. Read how to do this in the article "How to split a cell in Excel".
It is more convenient to work in a table where zeros are not visible in the cells. How to remove, hide, replace them, read the article "
When working in the Excel spreadsheet processor, the user faces various tasks, but even a seemingly simple action in it is not as easy to perform 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, the program does not have special tools for dividing cells into two, 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 involve dividing the cell diagonally, and vertically or horizontally. Without dwelling on the nuances for too long, let’s move directly to the methods of how to split a cell into two in Excel.
Method 1: through merging cells
As mentioned earlier, in order to divide a cell into two, you need to resort to some tricks. This is so because the cell is the primary structural element and cannot be split in two. Because of this, you can do the opposite: combine the cells in the table so that visually the necessary ones are separated.
- Clearly imagine how the table should look in the end.
- Select the two cells above those that are supposed to be separate.
- On the toolbar, click the “Merge and Center” button. It is located in the “Alignment” category.
Immediately after this, you will notice that all two columns of cells below the merged ones will appear separated, even though we did not perform any action on them. To see this more clearly, it is recommended to select the cell borders. To do this, follow these steps:
- Select the range of cells that are included in your table.
- On the toolbar, click the “Borders” button.
- From the drop-down menu, select "All Boundaries".
By the way, if you want to split a cell into two horizontally in Excel, then you just need to merge the two cells located to the right or left of it. In this case, the instructions for implementation 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. Therefore, we move on to them immediately.
Method 2: Split Merged Cells
And now we will tell you 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, or, to be more precise, to combine two adjacent columns. To do this:
- Launch the program.
- Holding the left mouse button on the column names (Latin letters located above the first row of the table), select two columns.
- While on the main application tab, click the arrow next to the “Alignment” button.
- From the drop-down menu that appears, select the “Merge by Rows” line.
Now instead of two columns you have one. This is where you can split a cell into two. To do this, you need:
- Click on the cell you want to split into two. It is important that it is in the column that is obtained after the merge.
- Go to the Home tab.
- In the “Align” tool group, click the “Merge and Center” button.
- From 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 about how to divide cell data in Excel into two, then, unfortunately, there are no special tools for this. You will need to use the usual Copy/Paste. It’s better to first build the table structure and only then enter values into it.
Method 3: Diagonal Divide Using Formatting
We have already established that in Excel it is impossible to divide a cell value into two; you will have to enter the information afterwards, but if you divide it diagonally, then no preliminary actions need to be taken. And all this is done by formatting:
- Open the workbook and go to the sheet in which you want to divide the cell.
- Select it.
- Then click the right mouse button.
- From 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 cell interface with the text “Inscription”. In the lower right or left corner there are buttons, after pressing which the cell is divided by a line diagonally from right to left or left to right, respectively.
- After this, you can also select the line type and color.
- After specifying all the necessary parameters, click OK.
After this, the cell will be split into two, and you can enter your values into it.
Method 4: Diagonal Splitting by Inserting a Shape
You can also split a cell diagonally using the Shapes tool. However, it is worth considering 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.
- From the drop-down list, select the object of the same name from the “Lines” category.
- Hold down the left mouse button and draw a line from one corner to the other.
After this, the cell will be divided into two diagonally.
Instead of a conclusion
So we figured out what needs to be done to separate cells in Excel. It is worth noting that you cannot split a cell with values into two in Excel using a special tool; this is done after simply moving the data. Otherwise, completing 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.
A worksheet 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, dividing a cell directly, as we say in Word, will not work. Because the address A5 will not become A/1-5 and A/2-5. So what can be done?
If you have not yet created a table and the data has not been entered, then initially think everything through well, and then simply merge certain cells, leaving them, for example, separated only in the header or in some row.
But let’s tell you in this article what to do if the table is ready. 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 a table like this. In it you must also indicate the Manufacturer after the Name.
Since new columns are added to the left of the current selection, I select any value in column C and, on the Home tab, insert columns.
So, after the Name, another vertical column appeared, which I will call Manufacturer.
Since fruits can be brought from different countries, you need to make several opposite each one in the added column from one cell.
Let's start with Apple. Select the block immediately below this name, right-click and select “Insert” from the context menu.
Mark the “line” with a marker in the box 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 Manufacturers.
Now let's merge the cells in those columns where the data does not change. In the example, these are Number, Name and Quantity. First you need to select them, then click on the “Merge” button and select “Cells”.
You can read about various ways to combine cells in Excel in the corresponding article.
Fill in the Manufacturer and Price fields with data.
As you can see, by adding rows and merging certain cells, we achieved that in the desired column we managed to divide them horizontally into several rows. Thus, Apple has several different Manufacturers and different Prices.
You can do the same with Pear. We add rows, combine where Number and Name are, 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 merged blocks you need to indicate the upper left address 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 subnames 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 undivided cell, it can be C2 or E2. Call the context menu and insert an additional line.
Using the appropriate button, combine D1 and E1, placing the value in the center.
In this cunning way, we divided the cell in the column under the word Quantity and gave them the names Imported and Sold. Where Sold, I still have previously merged cells with a value; if it doesn’t fit, I just need to cancel it by selecting the appropriate item.
As a result, we received 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, followed by combining some blocks.
To split diagonally, select the cell, call up the context menu by right-clicking and click “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 from one corner to another diagonally. If, as in the example, it is the wrong color, then on the tab "Drawing Tools" select the desired one either from ready-made styles, or by clicking on “Shape Outline”.
