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

Often, users of the Excel spreadsheet editor face such a task when it is necessary to delete all textual information in cells, and leave numerical data. There are many ways to carry out this procedure. In the article, we will analyze in detail each method that allows you to leave numbers and remove text in cells.

Methods for deleting text and preserving numerical information

Let’s imagine that we have such information located in the cell: “763 bars of soap were delivered”. We need to make it so that only the value 763 remains for various mathematical operations. Well, if you need to get rid of text data in only one cell, then you can implement the deletion manually, but this option is not suitable when there is too much different information in the plate. Here it is necessary to apply various special functions of the spreadsheet editor.

First method: using a special formula

To implement this procedure, you can use a special massive formula. An example formula looks like this:

=ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)))+1)

Let’s analyze the main points:

1. A special formula must be entered into the field using the Ctrl+Shift+Enter key combination.
2. It is worth noting that in this form, a massive formula can only be used with textual information, in which the number of characters does not exceed 99. To increase the range, you need, for example, to replace the parameter “ROW($1:$99)” on “ROW($1:$200)”. In other words, instead of the indicator 99, we enter the number of characters with a margin. If you enter too large a range, the formula may take a long time to process.
3. If the text data has numerical values ​​scattered throughout the text, then the formula will not be able to process the information correctly.

Let’s take a closer look at a special massive formula using the following example: “763 bars of soap were delivered, and 780 were ordered”

1. The field A1 contains the textual information itself, from which we will extract numerical data.
2. Fragment: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99))) allows you to determine the position of the 1st value in the field. We get the value 29.
3. Fragment: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)) allows you to determine the position of the last value in the field. We get the value 31.
4. We get this formula: =ПСТР(А1;29;31-29+1). The MID operator allows extracting from the text information specified by the 1st argument, starting from the specified position (29) with the number of characters specified by the 3rd argument.
5. As a result, we get:

   =ПСТР(А1;29;31-29+1)

   • =PSTR(A1;29;2+1)
   • =PSTR(A1;29;3)
   • 763

There are reverse situations when it is necessary to implement the operation of extracting one-part text data, excluding numbers.

We need to save only text information.

A special formula is applied according to a similar algorithm as the one discussed above. She looks like this: =ПСТР(А1;ПОИСК(«-«;А1)+1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР(ПСТР(А1;ПОИСК(«-«;А1)+1;999);СТРОКА($1:$99);1));0)-1)

Here, thanks to the SEARCH(“-“;A1) fragment, we found the location of the dash, and then, using the SEARCH operator, found the position of the 1st number in the extracted text data. We passed these indicators to the PSTR operator, which implemented all further transformations.

Second method: using a special macro

This procedure can be implemented using a special macro created in the Excel spreadsheet editor. For example, we have a plate in which there is a column with textual information and numerical data. We need to leave only numeric data, while removing the text.

The table editor lacks an integrated function, so we need to create a custom macro like this:

Public Function GetNumbers(TargetCell As Range) As String

Dim LenStr As Long

For LenStr = 1 To Len(TargetCell)

Select Case Asc(Mid(TargetCell, LenStr, 1))

Case 48 To 57

GetNumbers = GetNumbers & Mid(TargetCell, LenStr, 1)

End Select

Next

End Function

Detailed instructions for creating a custom macro look like this:

1. Using the special key combination “Alt + F11”, we open the VBA editor. An alternative option is to right-click on the worksheet and select the “Source Text” element.
2. Let’s create a new module. To carry out this procedure, left-click on the element named “Insert”, and then select the “Module” object.
3. We make a copy of the code, which is located above, and paste it into the created module. We implement copying using the keyboard shortcut “Ctrl + C”, and pasting – “Ctrl + V”.
4. Now in the required cell, in which we plan to display only numeric information, we drive in the following formula: =GetNumbers(А1).
5. We need to stretch the formula down to all the cells in the column. To do this, move the pointer to the lower right corner of the cell. The cursor has taken the form of a small black plus sign. Hold down the left mouse button and drag the formula down to the end of the plate.

6. Ready! We have implemented the extraction of numerical information using a special macro.

Conclusion and conclusions about the extraction procedure

We found out that there are several ways to implement the extraction of numerical information. You can perform this operation by creating special macros in the VBA editor or by using a massive formula. Each user can independently choose the most convenient way for himself, which will allow him to remove textual information from the cell and leave numeric data at the same time.