How to unhook text in Excel

Often, users of a spreadsheet editor are faced with the task of decoupling text. There are several ways to implement this simple procedure. In this article, we’ll take a closer look at each method.

Method One: Using an Automatic Tool

The table editor has a special automatic view tool that allows you to implement this procedure. Detailed instructions look like this:

1. We hold down the left mouse button and select all the cells that we plan to split into columns.

2. We move to the “Data” subsection and click LMB on the “Text by columns” element.

3. A window titled “Wizard Text Columns” appears. We put a fad next to the inscription “with separators”. Usually a space is used as a separator, but at a later stage you can edit the type of separation.

4. We put a mark next to the character that we plan to use as a separator. At the bottom of the window is the Data Parsing Sample field. Here you can preview the result.

5. At the next stage, we set a new column format, as well as the location to which the new information will be moved. After completing all the procedures, left-click on “Finish” to save the changes.

6. We return to the original plate and make sure that the disengagement process has occurred correctly.

It is worth noting! This method is suitable only in cases where only one unlinking is needed, specifying a new column for each word. This method is not suitable for work in which it is necessary to constantly carry out the tripping procedure, as a lot of time will be wasted. Consider the following methods.

Second Method: Applying the Text Decoupling Formula

This formula allows you to count the positions of words, find gaps, and then split all the words into separate columns. This method involves a large number of actions, so you need to carefully follow the steps of the detailed instructions. For example, we will take a field in which there are three words that are separated by spaces.

Stage One: Disconnecting the 1st Word

Detailed instructions look like this:

1. Create 3 new columns with headings.

2. We select the cell in which we plan to place the 1st word. Enter the formula here:=LEFT(.

3. Left-click on “Function Arguments”.

4. In the “Text” field, use the left mouse button to specify the coordinates of the cell. In our case, this is A1.

5. In the field “Number of_characters” we drive in the formula SEARCH(), which allows you to automatically count the number of characters up to the delimiter.

6. The formula is displayed in the line for entering formulas. Click on it LMB to get to the “Function Arguments” window.

7. In the line “Search_text” we drive in the necessary separator. The default is a space. In the line “Text_for_search” we drive in the coordinate A1.

8. Click LMB on the 1st function to again get into the window of its arguments. We add the value -2 to the right of the 1nd argument. This will allow the formula to take into account not the space itself, but the sign before it.

9. We close the arguments window to make sure that the information is displayed correctly.

10. Stretch the new cell down by pressing the left mouse button. The formula was automatically substituted in other cells.

Stage two: 2nd word decoupling

Decoupling the 2nd word is the most difficult stage, since it is surrounded by separators on 2 sides. Detailed instructions look like this:

1. To work, we need a formula =PSTR(. We write it in a cell, and then move to the window for entering arguments.

2. This formula allows you to find the required line in the text information. In the line “Text” enter the coordinate A1.

3. In the “Start_position” field, we drive in the SEARCH () formula, which allows you to automatically calculate the number of characters before the separator.

4. The formula is displayed in the line for entering formulas. Click on it LMB to get to the “Function Arguments” window.

5. In the line “Search_text” we drive in the necessary separator. The default is a space. In the line “Text_for_search” we drive in the coordinate A1.

6. Returning to the original window, add the value +1 to the end of the second argument. This will allow you to start counting from the next character after the found separator.

7. In the line “Number of_characters” again enter the formula SEARCH().

8. We fill in the operator arguments in the same way as above.

9. Additionally, here in the “Start_position” field, you must again write SEARCH(), to find the 2nd delimiter.

10. Operator Argument Box SEARCH() fill it in the same way as before.

11. Move on to the first SEARCH and add the value +1 to the start_position argument.

12. Click LMB on the element = PSTR, located in the line of formulas. Move the pointer to the end of the line “Number of_characters”.
13. Add value at the end -SEARCH(” “;A1)-1)to finish calculating the separators.

14. We close the arguments window to make sure that the information is displayed correctly.
15. Stretch the new cell down by pressing the left mouse button. The formula was automatically substituted in other cells.

Third stage: 3rd word decoupling

This stage is similar to the first, but has some minor differences. Detailed instructions look like this:

1. Enter the formula into the desired cell =RIGHT( and move to the arguments window.

2. In the line “Text” we drive in the coordinate A1.

3. In the line “Number of_characters” we drive in the formula DLSTR(A1). It allows you to determine the number of characters in the data.

4. We add to the end of this line the value familiar to us – SEARCH (). We move to the window of the arguments of this formula.

5. We drive in the data already familiar to us to search for a gap in the line.

6. In the line “Start_position” we drive in an additional SEARCH().

7. We provide the same information here.

8. We return to the past “SEARCH”.

9. At the end of the line “Start_position” add the value +1.

10. We confirm all the changes made.

11. Ready! We have implemented a 3-word uncoupling procedure. Each word is in a separate column.

Conclusion

We have looked at two ways to unlink text in a spreadsheet editor. The second method is the most effective, but its implementation requires a large number of actions. Understanding how the trip procedure works will make it much easier for users to work with the spreadsheet editor.