How to Extract Numbers from a String in Excel: Simple Techniques

Have you ever faced the need to extract numbers from a string in Excel? For instance, you have a long list of alphanumeric strings and want to extract only the numeric portion. This task can be challenging and time-consuming, especially if the list is lengthy. However, with the right tools and techniques, you can quickly and efficiently extract numbers from a string in Excel.

How to Extract Number from String Excel with Formula Method

One of the easiest ways to extract numbers from a string in Excel is by using formulas. With the following formula, you can extract numbers from a string:

=SUM(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

In this formula, "A1" represents the cell containing the alphanumeric string from which you want to extract numbers. You can replace "A1" with the appropriate cell reference. Once you have entered the formula, press "Enter," and Excel will extract the numbers from the string.

How to Extract Number from String Excel with Text to Columns Method

The Text to Columns method is another simple technique to extract numbers from a string in Excel.

1. Select the cells containing the alphanumeric strings and Click the "Data" tab.

2. Then, Click the "Text to Columns" button.

3. In the Text to Columns pop-up, select the "Delimited" option and click "Next."
   
   

4. In the next step, select the "Other" delimiter option and enter any non-numeric character that separates numbers from text, such as a space, comma, or hyphen. Click "Next."
   
   

5. In the final step, select the "Column data format" as "General" and click "Finish."

Excel will now separate the numbers from the text and place them into separate columns.

How to Extract Number from String Excel with Flash Fill Method

Flash Fill is a useful tool in Excel that automatically fills values in a column based on a pattern or a few examples. You can use the following steps to extract numbers from a string using Flash Fill:

1. Enter a sample alphanumeric string in the first cell of a new column.

2. In the next cell, enter the desired output format for the numeric portion of the string.

3. Press "Ctrl+E" to apply the Flash Fill.

Excel will now extract the numeric part of the string in the desired format.

How to Extract Number from String Excel with Nested Function Method

The Nested Function method is a more advanced technique to extract numbers from a string in Excel. It involves combining multiple functions to achieve the desired result.

1. Enter the alphanumeric string in a cell.

2. Use the following formula to extract the numeric portion of the string:

=1MID(A1,MATCH(TRUE,ISNUMBER(1MID(A1,ROW($1:$25),1)),0),SUMPRODUCT(--ISNUMBER(1MID(A1,ROW($1:$25),1)),ROW($1:$25))-MATCH(TRUE,ISNUMBER(1MID(A1,ROW($1:$25),1)),0)+1)

This formula will extract the numeric portion of the string and convert it into a number format.

Why You Should Choose WPS Office

In conclusion, extracting numbers from a string in Excel is a common task that can be achieved using various techniques. The formula, Text to Columns, Flash Fill, and Nested Function methods are commonly used to extract numbers from a string in Excel. WPS Spreadsheet is an excellent option for free and powerful spreadsheet software fully compatible with Excel.