How to leave only text in an Excel cell and remove all numbers

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:

  1. Use the left mouse button to select the cell of the source table where you want to uninstall the symbols.
  2. 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.
  3. 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.
How to leave only text in an Excel cell and remove all numbers
A number in an Excel spreadsheet cell that can be deleted by clicking on the “Backspace” button

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.

How to leave only text in an Excel cell and remove all numbers
The final result of the uninstall

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:

  1. Make a table with the original data. The table for which the character trim operation is performed is shown in the image below.
How to leave only text in an Excel cell and remove all numbers
Tabular array with initial data. From the first column, you need to remove the numbers before the text
  1. 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.
  2. 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.
How to leave only text in an Excel cell and remove all numbers
Finding the DLSTR Function in the Insert Window
  1. 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.
  2. 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.
How to leave only text in an Excel cell and remove all numbers
Filling in arguments for the function to calculate the length of a string
  1. 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.
How to leave only text in an Excel cell and remove all numbers
Extending a formula to automatically fill in the rest of the cells
  1. Calculate in your mind the number of numbers in the cell that need to be removed from it.
  2. 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.
How to leave only text in an Excel cell and remove all numbers
Formula for calculating the required number of characters in a cell, which will remain after deleting numbers
  1. 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.
  2. 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.

How to leave only text in an Excel cell and remove all numbers

  1. 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.
How to leave only text in an Excel cell and remove all numbers
The result of the formula

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:

  1. 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.
  2. In the second column of the tablet, write manually from the keyboard the numbers from the first column that need to be uninstalled.
  3. 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.
  4. 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.

Leave a Reply