How to split text into columns in Excel

Splitting text from one cell into multiple columns while keeping the original information and bringing it back to normal is a problem that every Excel user may encounter one day. Various methods are used to break text into columns, which are determined based on the information provided, the need to obtain the final result, and the degree of professionalism of the user.

It is necessary to separate the full name into separate columns

To complete the first example, let’s take a table with the full names of different people written in it. This is done using the Text by Columns tool. After compiling one of the documents, an error was discovered: last names, first names and patronymics are written in one column, which creates some inconvenience when filling out documents further. To obtain a qualitative result, it is necessary to perform the division of the full name into separate columns. How to do it – consider further. Description of actions:

1. We open the document with the error made earlier.
2. Select the text by holding the left mouse button and dragging the selection to the bottommost cell.

3. In the upper ribbon we find “Data” – go.

4. After opening, we find in the “Working with data” group “Text by columns”. Click LMB and go to the next dialog box.

5. By default, the input data format will be set to “delimited”. Leave and click on the “Next” button.
6. In the next window, we need to determine what is the separator in our text. We have this “space”, which means we check the box next to this value and agree with the actions taken by clicking on the “Next” button.

From an expert! Commas, periods, colons, semicolons, spaces, and other characters can be used to separate text.

7. Next, you need to define the format of the column data. The default is “General”. For our information, this format is the most appropriate.
8. In the table, select the cell where the formatted text will be placed. Let’s deviate from the source text one column and write the corresponding addressee in the addressing of the cell. When finished, click “Finish”.

Expert note! The placed formatted text, due to the different number of characters in the full name, may not fit in the selected cells, so the resulting table needs to be adjusted. To do this, expand cell sizes.

Splitting text with a formula

Complex formulas can be used to separate text independently. They are needed to accurately calculate the position of words in a cell, detect spaces, and divide each word into separate columns. For example, we will also use a table with a full name. To make the division, you will need to perform three steps.

Stage number 1. We transfer surnames

It will take the least time to separate the first word, because only one space needs to be built off to determine the correct position. Next, we will analyze step-by-step instructions in order to understand why calculations are needed in a particular case.

1. The table with the entered full name has already been created. For the convenience of separating information, create 3 columns in a separate area and write a definition at the top. Adjust the cell sizes.

2. Select the cell where information about the employee’s last name will be recorded. Activate it by pressing LMB.

3. Click on the “Arguments and Functions” button, the activation of which opens a window for editing the formula.
4. Here, in the “Category” section, you need to scroll down and select “Text”.

5. Next, we find the continuation of the formula LEFT and click on this line. We agree with the actions performed by pressing the “OK” button.
6. A new window appears where you need to specify the address of the cell that needs to be corrected. To do this, click on the “Text” column and activate the required cell. Addressing is entered automatically.
7. To specify the required number of characters, you can count them manually and enter the data in the appropriate column, or use another formula: SEARCH().
8. The formula will then appear in the text of the cell. Click on it to open the next window.

9. We find the field “Search text” and click on the separator indicated in the text. In our case, this is a gap.
10. In the “Text to search” field, you need to activate the editable cell, as a result of which the addressing will be automatically transferred.
11. Activate the first function to return to editing it. This action will automatically specify the number of characters before a space.

12. We agree and click on the “OK” button.

As a result, you can see that the cell is corrected and the last name is entered correctly. To apply changes to all lines, drag the selection handle down.

Stage number 2. Transferring names

It will take a little more effort and time to separate the second word, since the word is separated using two spaces.

1. As the main formula, we write in a similar way to the previous method =PSTR(.

2. Select a cell and indicate the position where the main text is written.
3. Go to the column “Starting position” and enter the formula SEARCH().
4. We pass to it, using the previous instruction.

5. In the line “Search text” indicate a space.
6. By clicking on “Text to search”, activate the cell.

7. Back to formula =PSTR at the top of the screen.
8. In the line “Starting position” we attribute +1 to the formula. This will help to start counting from the next character from the space.

9. We turn to determining the number of characters – we enter the formula SEARCH().
10. Follow this formula at the top and fill in all the data in a way that you already understand.
11. Now in the line “Starting position” you can enter a search formula. Activate one more transition according to the formula and fill in all the lines in a known way, without specifying anything in the “Start position”.
12. We turn to the previous SEARCH formula and add +1 to the “Start position”.
13. Back to formula =PSTR and in the line “Number of characters” we add the expression SEARCH(” “;A2)-1.

Stage number 3. We put the patronymic

1. By activating the cell and going to the function arguments, select the RIGHT formula. Click “OK”.

2. In the “Text” field, enter the address of the edited cell.
3. Where it is necessary to specify the number of characters, we write DLSTR(A2).

Expert note! The formula will automatically determine the number of characters.

4. To accurately determine the number of characters at the end, you must write: -SEARCH().
5. Go to formula editing. Enter a space in the Search Text. In “Text to search” – cell addressing. In “Start Position” insert the formula SEARCH(). Edit the formula with the same values.
6. Go to the previous SEARCH and add +1 to the “Starting position” line.

7. Go to the RIGHT formula and make sure everything is done correctly.

Conclusion

In the article, we got acquainted with two common ways of dividing information in cells into columns. Following simple instructions, you can easily master these methods and use them in practice. The complexity of splitting by columns using formulas can be off-putting for inexperienced Excel users at first, but the practical application of the method will help you get used to it and apply it in the future without any problems.