How to extract pure numbers from excel cells

How to extract pure numbers from Excel cells: 1. After entering the corresponding number in the first cell, select the area and press [Ctrl E] to quickly fill it; 2. Enter the formula [= in the cell MIDB(A2,SEARCHB("?",A2),2*LEN(A2)-LENB(A2))], and then fill downwards.

The operating environment of this article: Windows 7 system, Microsoft Office Excel 2010 version, Dell G3 computer.

How to extract pure numbers from excel cells:

First method: Ctrl E

first in the first cell Enter the corresponding number into the box, then select the area and press Ctrl E to quickly fill it.

If this function cannot be implemented in the form, then try the second method.

Second: Function method

Enter the formula in the cell=MIDB(A2,SEARCHB("?",A2),2*LEN (A2)-LENB(A2)), then fill downwards.

Formula description:

• ##SEARCHB("?",A2) : Indicates finding the position of the first number in cell A2

• 2*LEN(A2)-LENB(A2): Indicates extracting the number in the string The number

#Function description:

The SEARCHB function is used to find the starting position of the specified search value in the string

Grammar structure=SEARCHB(What to look for, where to look for, from which position to search)

LEN function returns the number of characters in the text string

Grammar structure=LEN(text String)

The MIDB function is to extract a string of a specific length starting from any position in the middle of a text string.

Grammar structure = MIDB (string, starting position, extraction length)

Related learning recommendations:

excel tutorial

The above is the detailed content of How to extract pure numbers from excel cells. For more information, please follow other related articles on the PHP Chinese website!