How to extract pure numbers from excel cells

Method: 1. Use the LEFT function to extract numbers from the left and return the specified number of characters; syntax "left(text,num_chars)". 2. Use the MID function to return characters of the specified length from the specified position, the syntax is "MID(text,start_num,num_chars)".

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

Open the Excel table that needs to be edited, as shown below:

If the cells contain pure numbers, the method of extracting numbers is relatively simple; for example, To extract the first three digits of the number in cell A2, you can enter the formula in cell B2:

=LEFT(A2,3)

The LEFT function means to extract the number from the left and return the specified number of characters.

If the cell contains a mixture of numbers and letters, the method of extracting numbers will have to change; for example, cell A3 where numbers are to be extracted is If you mix numbers and letters "abc123456", you can use the following formula:

 =MID(A3,4,6)

The MID function means to return characters of the specified length from the specified position.

If the cell contains a mixture of numbers and Chinese characters, one of the ways to extract numbers is to use the MID function; for example, cell A4 to extract numbers is If numbers and Chinese characters are mixed "This month's electricity bill is 128 yuan", you can use the following formula:

 =MID(A4,6,3)

The MID function means to return characters of a specified length from a specified position. Note: A Chinese character is still only counted as 1 digit.

The cell contains a mixture of numbers and Chinese characters. The second way to extract numbers is to find the pattern; for example, in this example, the end is " Yuan", then remove the "yuan" character first:

=LEFT(A4,LEN(A4)-LEN("元"))

Then calculate the digit through 2*LEN(B4)-LENB(B4), and then you can use RIGHT function to get the number in the cell:

=RIGHT(B4,2*LEN(B4)-LENB(B4))

For cell strings that can be converted to numbers followed by numbers, you can use the above formula; for example, cell A5 , you can also use the above method to complete digital extraction.

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!