Contents
In the cells of the table array of Microsoft Office Excel, you can prescribe various values, symbols. Accordingly, they can also be quickly removed, leaving only important and necessary elements. How to do this will be discussed in this article.
How to remove numbers from Excel cells leaving only text
There are several common ways to accomplish the task, each of which is recommended to study in detail in order to understand the principles of working in Excel.
Method 1: Manual removal
This method is considered the simplest. To implement it, the user will have to act as follows:
- Use the left mouse button to select the cell of the source table where you want to uninstall the symbols.
- Place the mouse cursor in the line for entering formulas at the top of the main menu of the program. This field contains the information contained in the selected cell.
- Put the pointer in the right place in the text and delete characters by pressing the “Backspace” button from the computer keyboard. Thus, in a cell, you can leave only the text itself, while deleting numerical values, values.
Pay attention! After uninstalling the elements in the line for entering formulas, they will be automatically removed from the specified cell of the table array.
Method 2: Using special functions
Microsoft Office Excel has built-in functions with which you can remove part of the characters from a specific table cell. When implementing the method, the user will need to use the functions: “DLSTR()” and “RIGHT()” or its analogue “LEFT()”. In general, the process of uninstalling numbers from Excel cells by entering the appropriate formulas is divided into the following steps:
- Make a table with the original data. The table for which the character trim operation is performed is shown in the image below.
- Select the cell where the result will be displayed and click LMB on the function icon to the right of the line for entering formulas.
- The “Insert Function” window opens. Here, in the search bar, you need to drive the name “DLSTR” from the keyboard to count the number of characters in the desired cell.
- Click twice LMB on the found value. After that, the function arguments window should start, and the operator itself will appear in the previously selected element of the table array.
- Place the mouse cursor in the “Text” line and in the original plate click on the line in which you want to count the number of characters.
- Click on “OK” and check the result. The corresponding value will be displayed in the selected column of the array. If necessary, the formula can be extended to the remaining rows of the table so as not to fill them in manually.
- Calculate in your mind the number of numbers in the cell that need to be removed from it.
- Now, from the total number of cell characters, you need to subtract the calculated value of unnecessary elements using the formula and display the result in a separate column. More details in the screenshot below.
- Proceed to uninstall extra characters, in this case numbers. To do this, in the second column of the plate, you need to register one more function.
- Write a function «=TRUESIM(S4;F4)”. In brackets in the first place is the address of the cell where the source text is written, and in the second place is the address of the element in which the number of characters remaining after deleting the numbers is counted.
- Press “Enter” from the PC keyboard to complete the formula and check the result. The selected column should display the original text, but without the numbers.
Important! The RIGHT() and LEFT() functions are equivalent. The first is used when it is necessary to remove the signs on the left, and the second, if the uninstallation is carried out to the right of the written formula.
If desired, the written expression in MS Excel can be extended to the entire range of cells of the table array so as not to perform the operation described above for each row.
Method 3: Extract numbers from text using flash fill option
This method does not involve the use of standard formulas. To remove numbers from Excel cells using the “Instant Fill” option, you need to follow a series of steps according to the algorithm:
- Create a source table. In this example, the table array consists of two columns. The first contains the full text, and the second will display the processed information without numbers.
- In the second column of the tablet, write manually from the keyboard the numbers from the first column that need to be uninstalled.
- Hold down the “Ctrl + E” buttons at the same time to activate the instant fill option. An option parameter icon will appear to the right of the completed second column of the table.
- After performing the previous manipulation, the numeric values from the first column of the table array will disappear. They will be listed in the second column.
Additional Information! You can cancel Flash Fill by using the keyboard shortcut “Ctrl + Z” on your computer keyboard in any version of Microsoft Office Excel.
Unfortunately, there is no special function in Excel that removes only numbers from the text in a cell or some specific characters. However, this possibility can be implemented in a longer way, described in the second method above.
Conclusion
Thus, the removal of numbers from cells is subject to a number of rules described above. The recommendations presented must be carefully read in order to have a good understanding of this topic.